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),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,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),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,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),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB from dbo.sysfiles a
go
....
edrz01Asked:
Who is Participating?
 
sachinpatil10dConnect With a Mentor Commented:
Try this

declare @dbName nvarchar(100)
declare @strSql nvarchar(1000)
declare @spdbdesc  table 
(  
 name sysname,  
 db_size nvarchar(100) null,  
 owner nvarchar(100),  
 dbid smallint,  
 created nvarchar(100),
 status nvarchar(600) null,  
 cmptlevel tinyint  
)  
insert into @spdbdesc
exec sp_helpdb
declare  cur cursor for
select name from @spdbdesc
open cur
fetch next from cur into @dbName
while @@FETCH_STATUS = 0
begin
	set @strSql = 'select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , 
				convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB , 
				convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB 
				from ' + @dbName + '.dbo.sysfiles a'
				print @strSql
	exec sp_executesql @strSql
	fetch next from cur into @dbName
end
close cur
deallocate cur

Open in new window

0
 
sachinpatil10dCommented:
you can even use  sys.databases  table to get all database names

declare @dbName nvarchar(100)
declare @strSql nvarchar(1000)
declare  cur cursor for
select name from sys.databases 
open cur
fetch next from cur into @dbName
while @@FETCH_STATUS = 0
begin
	set @strSql = 'select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , 
				convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB , 
				convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB 
				from ' + @dbName + '.dbo.sysfiles a'
				print @strSql
	exec sp_executesql @strSql
	fetch next from cur into @dbName
end
close cur
deallocate cur

Open in new window

0
 
edrz01Author Commented:
Thanks! The first solution work great. I tested the second one as well but feel that the first one is best suited.
0
 
sachinpatil10dCommented:
welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.