February 8, 2010 at 6:27 am
Hi Guys,
I built a query using temp table and recursion to get a report of file details of various databases on a server instance.
Could anyone please help me in converting this query to set based approach using CTE.
I need to learn CTEs to avoid use of temp tables. Any help would be appreciated.
declare @dbname nvarchar(45)
declare @sql nvarchar(1000)
declare @db_count table (cnt int identity(1,1), name nvarchar(100))
declare @ctr int, @var int
CREATE TABLE #result
( id int, name nvarchar(75), physical_name nvarchar(100),
fgname nvarchar(16), state_desc nvarchar(15), size nvarchar(20),
max_size nvarchar(20), Growth nvarchar(25))
set @ctr = 1
insert into @db_count select name from sys.databases
select @var = COUNT(*) from @db_count
while (@ctr <= @var)
BEGIN
select @dbname = name from @db_count where cnt = @ctr
set @sql = '
use ['+@dbname+']
select df.file_id,
df.name,
df.physical_name,
fg.name as Filegroup,
df.state_desc,
df.size*8/1024 as Size,
case df.max_size when -1 then ''Unlimited''
else cast (df.max_size as varchar(25))+ '' MB'' end as Max_Size,
case df.is_percent_growth when 1 then
cast(df.growth as varchar(3)) + '' %''
when 0 then cast (df.growth*8/1024 as varchar(10)) + '' MB''
end as Growth
from sys.database_files df left join sys.filegroups fg
on fg.data_space_id = df.data_space_id '
--print @sql
INSERT into #result
exec sp_executesql @sql
set @ctr = @ctr + 1
END
select * from #result
drop table #result
Regards
Akhil
February 8, 2010 at 6:52 am
Instead of using the system table database_files, you can use master_files table which contains data for all the databases.
select df.file_id,
df.name,
df.physical_name,
fg.name as Filegroup,
df.state_desc,
df.size*8/1024 as Size,
case df.max_size when -1 then 'Unlimited'
else cast (df.max_size as varchar(25))+ ' MB' end as Max_Size,
case df.is_percent_growth when 1 then
cast(df.growth as varchar(3)) + ' %'
when 0 then cast (df.growth*8/1024 as varchar(10)) + ' MB'
end as Growth
from sys.master_files df left join sys.filegroups fg
on fg.data_space_id = df.data_space_id
--Ramesh
February 8, 2010 at 7:23 am
I already tried that.
The results are incorrect. The filegroups are left out if the database has multiple filegroups.
I need to apply this query using CTE, but a little confused.
Regards
Akhil
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply