edrz01
asked on
Query List of Databases and then loop through to get information
I need to get the 'name' from running the 'exec sp_helpdb' and then use it in a loop whcih would run the following command against it.
use Database1 << This is the 'name' from the sp_helpdb
select name , filename , convert(decimal(12,2),roun d(a.size/1 28.000,2)) as FileSizeMB , convert(decimal(12,2),roun d(fileprop erty(a.nam e,'SpaceUs ed')/128.0 00,2)) as SpaceUsedMB , convert(decimal(12,2),roun d((a.size- fileproper ty(a.name, 'SpaceUsed '))/128.00 0,2)) as FreeSpaceMB from dbo.sysfiles a
go
use Database2 << This is the 'name' from the sp_helpdb
select name , filename , convert(decimal(12,2),roun d(a.size/1 28.000,2)) as FileSizeMB , convert(decimal(12,2),roun d(fileprop erty(a.nam e,'SpaceUs ed')/128.0 00,2)) as SpaceUsedMB , convert(decimal(12,2),roun d((a.size- fileproper ty(a.name, 'SpaceUsed '))/128.00 0,2)) as FreeSpaceMB from dbo.sysfiles a
go
use Database3 << This is the 'name' from the sp_helpdb
select name , filename , convert(decimal(12,2),roun d(a.size/1 28.000,2)) as FileSizeMB , convert(decimal(12,2),roun d(fileprop erty(a.nam e,'SpaceUs ed')/128.0 00,2)) as SpaceUsedMB , convert(decimal(12,2),roun d((a.size- fileproper ty(a.name, 'SpaceUsed '))/128.00 0,2)) as FreeSpaceMB from dbo.sysfiles a
go
....
use Database1 << This is the 'name' from the sp_helpdb
select name , filename , convert(decimal(12,2),roun
go
use Database2 << This is the 'name' from the sp_helpdb
select name , filename , convert(decimal(12,2),roun
go
use Database3 << This is the 'name' from the sp_helpdb
select name , filename , convert(decimal(12,2),roun
go
....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! The first solution work great. I tested the second one as well but feel that the first one is best suited.
welcome
Open in new window