March 19, 2008 at 8:58 am
Hello all.
Hope you can help me out or point me in the correct direction.
Looking to create a script I can run on each database instance that will retrieve database configuration details.
Looking to retireve the following.
Database name ,
SQL version ( @@ version) or something similar,
Sever name ,
Database data file size ,
Database Log file size ,
Replication (yes , no)
Had a look at SP_helpdb and some of that information seems fine , just need a little help on how to get the above listed as well.
any help appreciated.
many thanks all 🙂
March 19, 2008 at 9:13 am
El, try this:
select db_name() as databasename
, @@version as version
, @@servername as servername
, databasepropertyex(db_name(), 'IsPublished') as IsPublished
, str(convert(dec(15),sum(size))* (select convert(varchar(11),low) from master.dbo.spt_values where type = N'E' and number = 1)/ 1048576,10,2) + ' MB'
from dbo.sysfiles
March 19, 2008 at 9:20 am
Karl
many thanks for prompt reply.
Looks very useful , is there any way it can be run just once on each instance to return the stats for all the databases on there ?
System and User databases.
Is that possible ?
cheers
March 19, 2008 at 9:26 am
Sure,
Try this:
exec sp_msforeachdb '
select ''?'' as databasename
, @@version as version
, @@servername as servername
, databasepropertyex(''?'', ''IsPublished'') as IsPublished
, str(convert(dec(15),sum(size))* (select convert(varchar(11),low) from master.dbo.spt_values where type = N''E'' and number = 1)/ 1048576,10,2) + '' MB''
from ?.dbo.sysfiles'
March 19, 2008 at 9:36 am
Karl
Many thanks , looking great.
How would I change it to display results with only the column headers once ?
I.e. no requirement for each databases , only need it once , easier to export to excel etc.
cheers for all your help on this.
March 19, 2008 at 10:04 am
Hi El, not sure why I went for the sp_foreachdb method.
Try this instead....
select db_name(database_id) as databasename
, @@version as version
, @@servername as servername
, databasepropertyex(db_name(database_id), 'IsPublished') as IsPublished
, str(convert(dec(15),sum(size))* (select convert(varchar(11),low) from master.dbo.spt_values where type = N'E' and number = 1)/ 1048576,10,2) + ' MB'
from sys.master_files
group by database_id
March 19, 2008 at 10:13 am
Karl
Thanks for that.
getting this error ?
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Sys.master_files'.
Any ideas ?
March 19, 2008 at 11:01 am
Oops....I'm so used to using SQL 2005 I never bother to think that other's might be using SQL 2000. Use master.dbo.sysaltfiles instead of sys.master_files. You'll just need to change references from database_id to dbid.
March 20, 2008 at 2:38 am
😀
karl many thanks all works perfectly.
cheers
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply