scripting

I am a developer working with an application that requires connections with
mulitple databases on their respective servers. My question is concerned
with updating the multiple databases to keep the schemas the same. I am
using scripts generate using the Create Script command in Enterprise Manager
to create a script to drop and create all the functions, views and procs and
that works without any problem. My question is how to deal with the tables.
Is there some method for automatically creating a script that will add
missing tables and modify exiiting ones based on the source tables
collection. I don't want to use drop and create as for the functions, etc,
because I will lose all the data in the tables. The database schemas must b
e
the same but the data in each is different. If I have to create the script
by hand, it is just as easy to modify the tables by hand so what I am lookin
g
for is a command that says, "make all the tables the same"
JBJB
http://www.karaszi.com/SQLServer/in...rate_script.asp
"JB" <JB@.discussions.microsoft.com> wrote in message
news:3126220C-6899-4EDB-92C5-F3CB34AC9017@.microsoft.com...
>I am a developer working with an application that requires connections with
> mulitple databases on their respective servers. My question is concerned
> with updating the multiple databases to keep the schemas the same. I am
> using scripts generate using the Create Script command in Enterprise
> Manager
> to create a script to drop and create all the functions, views and procs
> and
> that works without any problem. My question is how to deal with the
> tables.
> Is there some method for automatically creating a script that will add
> missing tables and modify exiiting ones based on the source tables
> collection. I don't want to use drop and create as for the functions,
> etc,
> because I will lose all the data in the tables. The database schemas must
> be
> the same but the data in each is different. If I have to create the
> script
> by hand, it is just as easy to modify the tables by hand so what I am
> looking
> for is a command that says, "make all the tables the same"
>
> --
> JB|||You may find some of the Comparison tools useful. Most of them will compare,
and then create scripts to rectify the differences.
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL - SQL Diff
Best SoftTool - SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest - SchemaCompare
RAC4SQL's QALite (Free)
Red Gate - SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JB" <JB@.discussions.microsoft.com> wrote in message
news:3126220C-6899-4EDB-92C5-F3CB34AC9017@.microsoft.com...
>I am a developer working with an application that requires connections with
> mulitple databases on their respective servers. My question is concerned
> with updating the multiple databases to keep the schemas the same. I am
> using scripts generate using the Create Script command in Enterprise
> Manager
> to create a script to drop and create all the functions, views and procs
> and
> that works without any problem. My question is how to deal with the
> tables.
> Is there some method for automatically creating a script that will add
> missing tables and modify exiiting ones based on the source tables
> collection. I don't want to use drop and create as for the functions,
> etc,
> because I will lose all the data in the tables. The database schemas must
> be
> the same but the data in each is different. If I have to create the
> script
> by hand, it is just as easy to modify the tables by hand so what I am
> looking
> for is a command that says, "make all the tables the same"
>
> --
> JB

scripting

I am a developer working with an application that requires connections with
mulitple databases on their respective servers. My question is concerned
with updating the multiple databases to keep the schemas the same. I am
using scripts generate using the Create Script command in Enterprise Manager
to create a script to drop and create all the functions, views and procs and
that works without any problem. My question is how to deal with the tables.
Is there some method for automatically creating a script that will add
missing tables and modify exiiting ones based on the source tables
collection. I don't want to use drop and create as for the functions, etc,
because I will lose all the data in the tables. The database schemas must be
the same but the data in each is different. If I have to create the script
by hand, it is just as easy to modify the tables by hand so what I am looking
for is a command that says, "make all the tables the same"
JB
JB
http://www.karaszi.com/SQLServer/info_generate_script.asp
"JB" <JB@.discussions.microsoft.com> wrote in message
news:3126220C-6899-4EDB-92C5-F3CB34AC9017@.microsoft.com...
>I am a developer working with an application that requires connections with
> mulitple databases on their respective servers. My question is concerned
> with updating the multiple databases to keep the schemas the same. I am
> using scripts generate using the Create Script command in Enterprise
> Manager
> to create a script to drop and create all the functions, views and procs
> and
> that works without any problem. My question is how to deal with the
> tables.
> Is there some method for automatically creating a script that will add
> missing tables and modify exiiting ones based on the source tables
> collection. I don't want to use drop and create as for the functions,
> etc,
> because I will lose all the data in the tables. The database schemas must
> be
> the same but the data in each is different. If I have to create the
> script
> by hand, it is just as easy to modify the tables by hand so what I am
> looking
> for is a command that says, "make all the tables the same"
>
> --
> JB
|||You may find some of the Comparison tools useful. Most of them will compare,
and then create scripts to rectify the differences.
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL - SQL Diff
Best SoftTool - SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest - SchemaCompare
RAC4SQL's QALite (Free)
Red Gate - SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JB" <JB@.discussions.microsoft.com> wrote in message
news:3126220C-6899-4EDB-92C5-F3CB34AC9017@.microsoft.com...
>I am a developer working with an application that requires connections with
> mulitple databases on their respective servers. My question is concerned
> with updating the multiple databases to keep the schemas the same. I am
> using scripts generate using the Create Script command in Enterprise
> Manager
> to create a script to drop and create all the functions, views and procs
> and
> that works without any problem. My question is how to deal with the
> tables.
> Is there some method for automatically creating a script that will add
> missing tables and modify exiiting ones based on the source tables
> collection. I don't want to use drop and create as for the functions,
> etc,
> because I will lose all the data in the tables. The database schemas must
> be
> the same but the data in each is different. If I have to create the
> script
> by hand, it is just as easy to modify the tables by hand so what I am
> looking
> for is a command that says, "make all the tables the same"
>
> --
> JB

scripting

I am a developer working with an application that requires connections with
mulitple databases on their respective servers. My question is concerned
with updating the multiple databases to keep the schemas the same. I am
using scripts generate using the Create Script command in Enterprise Manager
to create a script to drop and create all the functions, views and procs and
that works without any problem. My question is how to deal with the tables.
Is there some method for automatically creating a script that will add
missing tables and modify exiiting ones based on the source tables
collection. I don't want to use drop and create as for the functions, etc,
because I will lose all the data in the tables. The database schemas must be
the same but the data in each is different. If I have to create the script
by hand, it is just as easy to modify the tables by hand so what I am looking
for is a command that says, "make all the tables the same"
--
JBJB
http://www.karaszi.com/SQLServer/info_generate_script.asp
"JB" <JB@.discussions.microsoft.com> wrote in message
news:3126220C-6899-4EDB-92C5-F3CB34AC9017@.microsoft.com...
>I am a developer working with an application that requires connections with
> mulitple databases on their respective servers. My question is concerned
> with updating the multiple databases to keep the schemas the same. I am
> using scripts generate using the Create Script command in Enterprise
> Manager
> to create a script to drop and create all the functions, views and procs
> and
> that works without any problem. My question is how to deal with the
> tables.
> Is there some method for automatically creating a script that will add
> missing tables and modify exiiting ones based on the source tables
> collection. I don't want to use drop and create as for the functions,
> etc,
> because I will lose all the data in the tables. The database schemas must
> be
> the same but the data in each is different. If I have to create the
> script
> by hand, it is just as easy to modify the tables by hand so what I am
> looking
> for is a command that says, "make all the tables the same"
>
> --
> JB|||You may find some of the Comparison tools useful. Most of them will compare,
and then create scripts to rectify the differences.
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL - SQL Diff
Best SoftTool - SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest - SchemaCompare
RAC4SQL's QALite (Free)
Red Gate - SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JB" <JB@.discussions.microsoft.com> wrote in message
news:3126220C-6899-4EDB-92C5-F3CB34AC9017@.microsoft.com...
>I am a developer working with an application that requires connections with
> mulitple databases on their respective servers. My question is concerned
> with updating the multiple databases to keep the schemas the same. I am
> using scripts generate using the Create Script command in Enterprise
> Manager
> to create a script to drop and create all the functions, views and procs
> and
> that works without any problem. My question is how to deal with the
> tables.
> Is there some method for automatically creating a script that will add
> missing tables and modify exiiting ones based on the source tables
> collection. I don't want to use drop and create as for the functions,
> etc,
> because I will lose all the data in the tables. The database schemas must
> be
> the same but the data in each is different. If I have to create the
> script
> by hand, it is just as easy to modify the tables by hand so what I am
> looking
> for is a command that says, "make all the tables the same"
>
> --
> JB

Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

Script which will shrink all databases

Hi,
May somebody send script which will shrink all databases on server
(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
I need do this at many servers, so I can't do this manually.
Thanks
RegardsOn Aug 9, 12:41 pm, anxc...@.gmail.com wrote:
> Hi,
> May somebody send script which will shrink all databases on server
> (SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
> I need do this at many servers, so I can't do this manually.
> Thanks
> --
> Regards
There are multiple ways of doing this.
Create a .CMD file with this statement:
----
----
sqlcmd -s <servername1> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername2> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername3> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername4> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername5> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
----
----
Replace the <servername> with your servername
For each server add a new line and with the changed servername
(to create a CMD file: open notepad and paste the final script and
save it as scriptfile.cmd)
Once you have the CMD file, open commandprompt and go to the drive
location and enter the name of the file
In my case it is:
----
----
c:\dbccscript.cmd
----
----
This will display all the output to the commandprompt. You can
redirect all the output to a log/txt file using -o switch in the cmd
script.
Note that sqlcmd is a new utility in MS SQL server 2005.
in SQL Server 2000 you can achieve the same using "OSQL" books online
has enuf info on this to help you rewrite it for osql.
Regards,
schal|||Thanks shal, unfortunately I don't have "shell - cmd" access to
server, I have to shrink all databases from SQL script only.|||What schal posted will shrink all databases for the server you are connected
to:
EXEC sp_MSForEachDB @.Command1 = N'DBCC SHRINKDATABASE (?, 10)', @.replacechar
= '?'"
But I strongly suggest you read http://www.karaszi.com/SQLServer/in...ink.
asp first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1186682763.588386.295740@.19g2000hsx.googlegroups.c
om...
> Thanks shal, unfortunately I don't have "shell - cmd" access to
> server, I have to shrink all databases from SQL script only.
>|||It really works, I forgot that is normal storage procedure which I can
run from Query, sorry.
But what if I'd like first use SHRINKFILE all log files (all .LDF
files at server), log files because it takes me much more space than
databases? Logical name of log file is usually DatabaseName_Log.
How shrink log file for all databases, is it similar procedure?|||Try this!
It′s recovery system space quickly:
use master
go
declare curdb cursor for select name from sysdatabases where name not in
('tempdb','model','master')
declare @.nmdb varchar(50)
declare @.cmd varchar(1000)
open curdb
fetch next from curdb into @.nmdb
while @.@.fetch_status = 0
begin
print '======='
print @.nmdb
print '======='
select @.cmd = 'Use ' + @.nmdb
select @.cmd = @.cmd + ' if exists(select 1 from sysobjects where name =
''limpa'')
begin
DROP TABLE LIMPA
end
create table limpa (char1 char(4000))
Set NoCount On
dbcc shrinkFILE (2, notruncate)
dbcc shrinkFILE (2, truncateonly)
dbcc shrinkFILE (1, notruncate)
dbcc shrinkFILE (1, truncateonly)
declare @.i int
select @.i = 0
While (@.i < 100)
begin
insert into limpa values (''a'')
select @.i =@.i + 1
end
backup log ' + @.nmdb + ' with truncate_only
drop table limpa'
Execute (@.cmd)
fetch next from curdb into @.nmdb
end
deallocate curdb
set nocount off
anxcomp@.gmail.com wrote:
>Hi,
>May somebody send script which will shrink all databases on server
>(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
>I need do this at many servers, so I can't do this manually.
>Thanks
>--
>Regards
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1|||Thanks, if thought it is simples way to do this. Could you me tell me
how at loop take:
1. name for all databases (without system dbs)
2. logical name of databases
2. logical name of transaction logs.
I need this information for this
BACKUP LOG @.DB_NAME
DBCC SHRINKFILE (@.DB_LOG_NAME)
I'd like this two lines will execute for all database.
Thanks
Regards|||I wrote my own script and I'd like share it with you:
CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb',
'distribution') AND type_desc LIKE 'log'
SET NOCOUNT ON
DECLARE @.VarDBLogicalName nvarchar(128)
DECLARE @.VarDBName nvarchar(128)
DECLARE @.VarRowCount int
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.rowcount
WHILE @.VarRowCount <> 0
BEGIN
EXEC(' use ' + @.VarDBName + ' backup log '+ @.VarDBName + ' with no_log
dbcc shrinkfile(''' + @.VarDBLogicalName + ''', TRUNCATEONLY) WITH
NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @.VarDBName
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName =
DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF
This works but use something what Microsoft NOT recommend "backup log
with no_log" you can modify it how you like
Regards

Script which will shrink all databases

Hi,
May somebody send script which will shrink all databases on server
(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
I need do this at many servers, so I can't do this manually.
Thanks
Regards
On Aug 9, 12:41 pm, anxc...@.gmail.com wrote:
> Hi,
> May somebody send script which will shrink all databases on server
> (SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
> I need do this at many servers, so I can't do this manually.
> Thanks
> --
> Regards
There are multiple ways of doing this.
Create a .CMD file with this statement:
------
sqlcmd -s <servername1> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername2> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername3> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername4> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername5> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
------
Replace the <servername> with your servername
For each server add a new line and with the changed servername
(to create a CMD file: open notepad and paste the final script and
save it as scriptfile.cmd)
Once you have the CMD file, open commandprompt and go to the drive
location and enter the name of the file
In my case it is:
------
c:\dbccscript.cmd
------
This will display all the output to the commandprompt. You can
redirect all the output to a log/txt file using -o switch in the cmd
script.
Note that sqlcmd is a new utility in MS SQL server 2005.
in SQL Server 2000 you can achieve the same using "OSQL" books online
has enuf info on this to help you rewrite it for osql.
Regards,
schal
|||Thanks shal, unfortunately I don't have "shell - cmd" access to
server, I have to shrink all databases from SQL script only.
|||What schal posted will shrink all databases for the server you are connected to:
EXEC sp_MSForEachDB @.Command1 = N'DBCC SHRINKDATABASE (?, 10)', @.replacechar = '?'"
But I strongly suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1186682763.588386.295740@.19g2000hsx.googlegro ups.com...
> Thanks shal, unfortunately I don't have "shell - cmd" access to
> server, I have to shrink all databases from SQL script only.
>
|||It really works, I forgot that is normal storage procedure which I can
run from Query, sorry.
But what if I'd like first use SHRINKFILE all log files (all .LDF
files at server), log files because it takes me much more space than
databases? Logical name of log file is usually DatabaseName_Log.
How shrink log file for all databases, is it similar procedure?
|||Try this!
It′s recovery system space quickly:
use master
go
declare curdb cursor for select name from sysdatabases where name not in
('tempdb','model','master')
declare @.nmdb varchar(50)
declare @.cmd varchar(1000)
open curdb
fetch next from curdb into @.nmdb
while @.@.fetch_status = 0
begin
print '======='
print @.nmdb
print '======='
select @.cmd = 'Use ' + @.nmdb
select @.cmd = @.cmd + ' if exists(select 1 from sysobjects where name =
''limpa'')
begin
DROP TABLE LIMPA
end
create table limpa (char1 char(4000))
Set NoCount On
dbcc shrinkFILE (2, notruncate)
dbcc shrinkFILE (2, truncateonly)
dbcc shrinkFILE (1, notruncate)
dbcc shrinkFILE (1, truncateonly)
declare @.i int
select @.i = 0
While (@.i < 100)
begin
insert into limpa values (''a'')
select @.i =@.i + 1
end
backup log ' + @.nmdb + ' with truncate_only
drop table limpa'
Execute (@.cmd)
fetch next from curdb into @.nmdb
end
deallocate curdb
set nocount off
anxcomp@.gmail.com wrote:
>Hi,
>May somebody send script which will shrink all databases on server
>(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
>I need do this at many servers, so I can't do this manually.
>Thanks
>--
>Regards
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
|||Thanks, if thought it is simples way to do this. Could you me tell me
how at loop take:
1. name for all databases (without system dbs)
2. logical name of databases
2. logical name of transaction logs.
I need this information for this
BACKUP LOG @.DB_NAME
DBCC SHRINKFILE (@.DB_LOG_NAME)
I'd like this two lines will execute for all database.
Thanks
Regards
|||I wrote my own script and I'd like share it with you:
CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb',
'distribution') AND type_desc LIKE 'log'
SET NOCOUNT ON
DECLARE @.VarDBLogicalName nvarchar(128)
DECLARE @.VarDBName nvarchar(128)
DECLARE @.VarRowCount int
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.rowcount
WHILE @.VarRowCount <> 0
BEGIN
EXEC(' use ' + @.VarDBName + ' backup log '+ @.VarDBName + ' with no_log
dbcc shrinkfile(''' + @.VarDBLogicalName + ''', TRUNCATEONLY) WITH
NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @.VarDBName
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName =
DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF
This works but use something what Microsoft NOT recommend "backup log
with no_log" you can modify it how you like
Regards

Script which will shrink all databases

Hi,
May somebody send script which will shrink all databases on server
(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
I need do this at many servers, so I can't do this manually.
Thanks
--
RegardsOn Aug 9, 12:41 pm, anxc...@.gmail.com wrote:
> Hi,
> May somebody send script which will shrink all databases on server
> (SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
> I need do this at many servers, so I can't do this manually.
> Thanks
> --
> Regards
There are multiple ways of doing this.
Create a .CMD file with this statement:
------
sqlcmd -s <servername1> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername2> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername3> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername4> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
sqlcmd -s <servername5> -Q "EXEC sp_MSForEachDB @.Command1 = N'DBCC
SHRINKDATABASE (?, 10)', @.replacechar = '?'" -c go
------
Replace the <servername> with your servername
For each server add a new line and with the changed servername
(to create a CMD file: open notepad and paste the final script and
save it as scriptfile.cmd)
Once you have the CMD file, open commandprompt and go to the drive
location and enter the name of the file
In my case it is:
------
c:\dbccscript.cmd
------
This will display all the output to the commandprompt. You can
redirect all the output to a log/txt file using -o switch in the cmd
script.
Note that sqlcmd is a new utility in MS SQL server 2005.
in SQL Server 2000 you can achieve the same using "OSQL" books online
has enuf info on this to help you rewrite it for osql.
Regards,
schal|||Thanks shal, unfortunately I don't have "shell - cmd" access to
server, I have to shrink all databases from SQL script only.|||What schal posted will shrink all databases for the server you are connected to:
EXEC sp_MSForEachDB @.Command1 = N'DBCC SHRINKDATABASE (?, 10)', @.replacechar = '?'"
But I strongly suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1186682763.588386.295740@.19g2000hsx.googlegroups.com...
> Thanks shal, unfortunately I don't have "shell - cmd" access to
> server, I have to shrink all databases from SQL script only.
>|||It really works, I forgot that is normal storage procedure which I can
run from Query, sorry.
But what if I'd like first use SHRINKFILE all log files (all .LDF
files at server), log files because it takes me much more space than
databases? Logical name of log file is usually DatabaseName_Log.
How shrink log file for all databases, is it similar procedure?|||Try this!
It´s recovery system space quickly:
use master
go
declare curdb cursor for select name from sysdatabases where name not in
('tempdb','model','master')
declare @.nmdb varchar(50)
declare @.cmd varchar(1000)
open curdb
fetch next from curdb into @.nmdb
while @.@.fetch_status = 0
begin
print '======='
print @.nmdb
print '======='
select @.cmd = 'Use ' + @.nmdb
select @.cmd = @.cmd + ' if exists(select 1 from sysobjects where name =''limpa'')
begin
DROP TABLE LIMPA
end
create table limpa (char1 char(4000))
Set NoCount On
dbcc shrinkFILE (2, notruncate)
dbcc shrinkFILE (2, truncateonly)
dbcc shrinkFILE (1, notruncate)
dbcc shrinkFILE (1, truncateonly)
declare @.i int
select @.i = 0
While (@.i < 100)
begin
insert into limpa values (''a'')
select @.i =@.i + 1
end
backup log ' + @.nmdb + ' with truncate_only
drop table limpa'
Execute (@.cmd)
fetch next from curdb into @.nmdb
end
deallocate curdb
set nocount off
anxcomp@.gmail.com wrote:
>Hi,
>May somebody send script which will shrink all databases on server
>(SQL 2005). I think about DBCC SHRINKFILE and DBCC SHRINKDATABASE.
>I need do this at many servers, so I can't do this manually.
>Thanks
>--
>Regards
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||Thanks, if thought it is simples way to do this. Could you me tell me
how at loop take:
1. name for all databases (without system dbs)
2. logical name of databases
2. logical name of transaction logs.
I need this information for this
BACKUP LOG @.DB_NAME
DBCC SHRINKFILE (@.DB_LOG_NAME)
I'd like this two lines will execute for all database.
Thanks
--
Regards|||I wrote my own script and I'd like share it with you:
CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb',
'distribution') AND type_desc LIKE 'log'
SET NOCOUNT ON
DECLARE @.VarDBLogicalName nvarchar(128)
DECLARE @.VarDBName nvarchar(128)
DECLARE @.VarRowCount int
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.rowcount
WHILE @.VarRowCount <> 0
BEGIN
EXEC(' use ' + @.VarDBName + ' backup log '+ @.VarDBName + ' with no_log
dbcc shrinkfile(''' + @.VarDBLogicalName + ''', TRUNCATEONLY) WITH
NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @.VarDBName
SELECT top 1 @.VarDBName = DBName, @.VarDBLogicalName =DBLogicalName
FROM #TDatabases
SET @.VarRowCount = @.@.ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF
This works but use something what Microsoft NOT recommend "backup log
with no_log" you can modify it how you like
--
Regardssql

script to shows all dbs on all servers.

I am writing a script that would give me databases of all servers. I also
need to know whats the database size fo each database on each server. The
way i get teh first one working was set up linked server to all databases
that i need the information from. Then for each server get the databases
from sysdatabases. I run into problem when i need to know the database
size. sp_spaceused returns 2 rows for a database is there any procedure
that i can use in msforeachdb to get the database size. Or is there nay
other means to do this. any help will be greatly appreciated.
TIA
this is the text for my existing proc.
CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
AS
DECLARE @.ServerName varchar(100),
@.return int,
@.sql nvarchar(1000)
SET NOCOUNT ON
if exists (select * from master.dbo.sysobjects where name = 'Details')
BEGIN
truncate table master..Details
end
else
begin
Create Table Master..Details
( ServerName Char(100),
DatabaseName Char(100))
end
DECLARE server_cursor CURSOR
FOR
SELECT srvname FROM master..sysservers
where srvname not in ('repl_distributor')
OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @.ServerName
WHILE (@.@.fetch_status <> -1)
BEGIN
SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName)
+']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
--select @.sql
exec sp_executesql @.sql
FETCH NEXT FROM server_cursor INTO @.ServerName
END
DEALLOCATE server_cursor
SELECT * from master..Details
SET NOCOUNT OFF
GO
Message posted via http://www.webservertalk.comKilobytes of all files used by the current database (data and log files):
SELECT SUM([size]) * 8 FROM dbo.sysfiles
sysfiles.size is the number of pages used by the db; each page is 8 kb; so
size * 8 = kb. multiply by 1024 to get bytes, or divide by 1024 to get mb,
etc.
"ishaan99 via webservertalk.com" wrote:

> I am writing a script that would give me databases of all servers. I also
> need to know whats the database size fo each database on each server. The
> way i get teh first one working was set up linked server to all databases
> that i need the information from. Then for each server get the databases
> from sysdatabases. I run into problem when i need to know the database
> size. sp_spaceused returns 2 rows for a database is there any procedure
> that i can use in msforeachdb to get the database size. Or is there nay
> other means to do this. any help will be greatly appreciated.
> TIA
> this is the text for my existing proc.
> CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
>
> AS
> DECLARE @.ServerName varchar(100),
> @.return int,
> @.sql nvarchar(1000)
> SET NOCOUNT ON
> if exists (select * from master.dbo.sysobjects where name = 'Details')
> BEGIN
> truncate table master..Details
> end
> else
> begin
>
> Create Table Master..Details
> ( ServerName Char(100),
> DatabaseName Char(100))
> end
> DECLARE server_cursor CURSOR
> FOR
> SELECT srvname FROM master..sysservers
> where srvname not in ('repl_distributor')
> OPEN server_cursor
> FETCH NEXT FROM server_cursor INTO @.ServerName
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName
)
> +']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
> --select @.sql
> exec sp_executesql @.sql
> FETCH NEXT FROM server_cursor INTO @.ServerName
> END
> DEALLOCATE server_cursor
> SELECT * from master..Details
> SET NOCOUNT OFF
> GO
> --
> Message posted via http://www.webservertalk.com
>sql

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname> ,'phsical_mdf_with_path','new_ldf_with_p
ath'
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>|||... also you can use sp_renamedb to rename a database, but follow Hari's so
lution to change the logical & physical filenames.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"Hari Prasad" wrote:

> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname> ,'phsical_mdf_with_path','new_ldf_with_p
ath'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> a
>
>

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.
Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_pat h'
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>
|||... also you can use sp_renamedb to rename a database, but follow Hari's solution to change the logical & physical filenames.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hari Prasad" wrote:

> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_pat h'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> a
>
>

Script to rename database (and files)

I need to rename several databases and also rename the data and log files
(since these filenames contain the DB name). I was thinking of writing a
script to do so but before I did, I thought I would ask if anyone had such a
script. If someone happens to have something like this - I would greatly
appreciate it.
Thanks in advance.Hi,
To rename the logical name of the file:- use the below script
alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
='NEW_MDF_NAME')
Do the same for LDF file as well.
For renaming the database.
alter database OLD_DB_NAME modify name = new_db_name
To rename the physical files. Usae the below script sample.
sp_detach_db <dbname>
go
--rename the LDF file and MDF physical files using windows explorer
go
sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_path'
--
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> I need to rename several databases and also rename the data and log files
> (since these filenames contain the DB name). I was thinking of writing a
> script to do so but before I did, I thought I would ask if anyone had such
a
> script. If someone happens to have something like this - I would greatly
> appreciate it.
> Thanks in advance.
>|||... also you can use sp_renamedb to rename a database, but follow Hari's solution to change the logical & physical filenames.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hari Prasad" wrote:
> Hi,
> To rename the logical name of the file:- use the below script
> alter database DBNAME modify file (NAME = 'old_MDF_NAME', NEWNAME
> ='NEW_MDF_NAME')
> Do the same for LDF file as well.
>
> For renaming the database.
> alter database OLD_DB_NAME modify name = new_db_name
>
> To rename the physical files. Usae the below script sample.
> sp_detach_db <dbname>
> go
> --rename the LDF file and MDF physical files using windows explorer
> go
> sp_attach_db <Dbname>,'phsical_mdf_with_path','new_ldf_with_path'
> --
> Thanks
> Hari
> MCDBA
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:uWmfziZaEHA.2632@.TK2MSFTNGP10.phx.gbl...
> > I need to rename several databases and also rename the data and log files
> > (since these filenames contain the DB name). I was thinking of writing a
> > script to do so but before I did, I thought I would ask if anyone had such
> a
> > script. If someone happens to have something like this - I would greatly
> > appreciate it.
> >
> > Thanks in advance.
> >
> >
>
>

Script to know whether database backups are being done on SQL SERVER databases ??

Hi All,
I want to have a script which will let me know whether all the
databases in a SQL SERVER are being backed up.I tried using
sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
the databases for which they are being backedup.
TIA,
AKIf you're using maintenance plans, you can query like this:
SELECT TOP 10 *
FROM msdb.dbo.sysdbmaintplan_history
WHERE activity = 'Backup database'
Andre
"AkB" <anilkb@.gmail.com> wrote in message
news:47ce6498.0410191209.612cca5e@.posting.google.com...
> Hi All,
> I want to have a script which will let me know whether all the
> databases in a SQL SERVER are being backed up.I tried using
> sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
> the databases for which they are being backedup.
> TIA,
> AKsql

Script to know whether database backups are being done on SQL SERVER databases ??

Hi All,
I want to have a script which will let me know whether all the
databases in a SQL SERVER are being backed up.I tried using
sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
the databases for which they are being backedup.
TIA,
AK
If you're using maintenance plans, you can query like this:
SELECT TOP 10 *
FROM msdb.dbo.sysdbmaintplan_history
WHERE activity = 'Backup database'
Andre
"AkB" <anilkb@.gmail.com> wrote in message
news:47ce6498.0410191209.612cca5e@.posting.google.c om...
> Hi All,
> I want to have a script which will let me know whether all the
> databases in a SQL SERVER are being backed up.I tried using
> sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
> the databases for which they are being backedup.
> TIA,
> AK

Script to know whether database backups are being done on SQL SERVER databases ??

Hi All,
I want to have a script which will let me know whether all the
databases in a SQL SERVER are being backed up.I tried using
sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
the databases for which they are being backedup.
TIA,
AKIf you're using maintenance plans, you can query like this:
SELECT TOP 10 *
FROM msdb.dbo.sysdbmaintplan_history
WHERE activity = 'Backup database'
Andre
"AkB" <anilkb@.gmail.com> wrote in message
news:47ce6498.0410191209.612cca5e@.posting.google.com...
> Hi All,
> I want to have a script which will let me know whether all the
> databases in a SQL SERVER are being backed up.I tried using
> sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
> the databases for which they are being backedup.
> TIA,
> AK

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanks
Hi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>

Script to enumerate user databases on a remote server

Hello there
I want to know if there is an already written script outhere that can give
me an enumeration of all user databases, and theire size ?
My end goal is to have a webpage I can logon into and I can check on the
report for sizes, backup jobs and .... if there is anyproduct that already
aggregate such data for multiple servers (12 in my case) then it will be
great.
thanksHi,
You could use the system stored procedures
sp_helpdb
or
sp_databases
You use could use OSQL and execute this stored procedure in all server and
insert the data into one common table and then use that table in
displaying the web page.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> Hello there
> I want to know if there is an already written script outhere that can give
> me an enumeration of all user databases, and theire size ?
> My end goal is to have a webpage I can logon into and I can check on the
> report for sizes, backup jobs and .... if there is anyproduct that
> already aggregate such data for multiple servers (12 in my case) then it
> will be great.
> thanks
>
>|||Hari Prasad wrote:[vbcol=seagreen]
> *Hi,
> You could use the system stored procedures
> sp_helpdb
> or
> sp_databases
> You use could use OSQL and execute this stored procedure in all
> server and
> insert the data into one common table and then use that table in
> displaying the web page.
> Thanks
> Hari
> SQL Server MVP
>
> "Simo Sentissi" <msentissi@.rightnow.com> wrote in message
> news:eAiu706lFHA.3568@.tk2msftngp13.phx.gbl...
> can give
> on the
> that
> then it
Have you found an answer to this yet? If not I have script for you.
gills
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1768228.htmlsql