How to use DBCC SHOWFILESTATS result in temp table

 

DBCC showfilestats will give you the number of pages are used in extents and number of filgroups are generated for sql server database. If you want to store these information into temporary table then you can use the below query.

if not exists (select 1 from sys.objects where object_id = object_id(‘#tmpfilestats’))
begin
drop table #tmpfilestats
end

create table #tmpfilestats
([Fileid] int,
[FileGroup] int,
[TotalExtents] int,
[UsedExtents] int,
[LogicalName] varchar(20),
[FileName] nchar(200))

insert into #tmpfilestats exec (‘DBCC SHOWFILESTATS’)

select * from #tmpfilestats

Generate File stats using DBCC SHOWFILESTATS for all databases

if not exists (select 1 from sys.objects where object_id = object_id(‘#tmpfilestats’))
begin
drop table #tmpfilestats
end

create table #tmpfilestats
([Fileid] int,
[FileGroup] int,
[TotalExtents] int,
[UsedExtents] int,
[LogicalName] sysname,
[FileName] nchar(500))

declare @dbname varchar(50)
declare cursorDB cursor for
select ltrim(rtrim(name)) from sys.databases
where state_desc=’ONLINE’
and user_access_desc=’MULTI_USER’
open cursorDB
fetch cursorDB into @dbname
while @@fetch_status = 0
begin
insert into # tmpfilestats exec (‘USE [‘ + @dbname + ‘] DBCC SHOWFILESTATS’)
fetch cursorDB into @dbname
end
close cursorDB
deallocate cursorDB

Leave a Reply

  

  

  


+ 3 = 9