Display data fragment usage by database

drop proc sp_dbusage
GO

create proc sp_dbusage
as
declare dbidcur cursor for select dbid from master.dbo.sysdatabases order by dbid

declare @numpgsmb       float
declare @pagekb         int
declare @curdbid int

select @numpgsmb = (1048576. / v.low)
        from master.dbo.spt_values v
                 where v.number = 1
                 and v.type = "E"
select @pagekb = (low / 1024)
        from master.dbo.spt_values
        where number = 1
          and type = 'E'

create table #dbusage (
dbname varchar(16),
fragname varchar(16),
usage char(16),
sizeKB int,
freeKB int

)
open dbidcur
fetch dbidcur into @curdbid
while (@@sqlstatus = 0)
begin

insert into #dbusage
 select db_name(@curdbid), v.name,
 m.description,
             size * @pagekb,
   

          case
             when u.segmap = 4 then -1
             else
             (curunreservedpgs(@curdbid, u.lstart,
                     u.unreservedpgs) * @pagekb)
     end
 from master.dbo.sysdatabases d,
      sysusages u,
      master.dbo.sysdevices v,
      master.dbo.spt_values a,
      master.dbo.spt_values b,
      master.dbo.sysmessages m
     where d.dbid = u.dbid
             and v.low <= u.size + vstart
             and v.high >= u.size + vstart - 1
             and v.status & 2 = 2
             and d.name = db_name(@curdbid)
             and a.type = "E"
             and a.number = 1
             and b.type = "S"
             and u.segmap & 7 = b.number
            and b.msgnum = m.error
           
 order by 1

 fetch dbidcur into @curdbid
end
close dbidcur
deallocate cursor dbidcur
select *, convert(numeric(10, 2), freeKB * 100.0/sizeKB)  as "free(%)" from #dbusage where freeKB != 1
select
 dbname,
 sum(sizeKB) as "data size(KB)",
 sum(freeKB) as "data free(KB)",
 convert(numeric(10,2), sum(freeKB)*100.0/sum(sizeKB)) as "data free(%)"
from
 #dbusage where freeKB != -1  group by dbname order by 4

GO

the sample out put is as below:

 

\	dbname	fragname	usage	sizeKB	freeKB	free(%)
1	master	master	data and log	6144	1898	30.89
2	master	master	data and log	55296	50808	91.88
3	tempdb	master	data and log	3072	1848	60.15
4	tempdb	temp_device	data and log	307200	305970	99.59
5	model	master	data and log	2048	830	40.52
6	keno	hotlot_dat01	data only	1024000	873436	85.29
7	keno	hotlot_dat02	data only	1024000	1020000	99.60
8	keno	hotlot_log01	log only	512000	-1	0.00
9	open_database	opendb_dat01	data only	204800	199472	97.39
10	open_database	opendb_log01	log only	102400	-1	0.00
11	lottery	hotlot_dat11	data only	1024000	931968	91.01
12	lottery	hotlot_dat12	data only	1024000	1020000	99.60
13	lottery	hotlot_log11	log only	512000	-1	0.00
14	sybsystemdb	master	data and log	2048	766	37.40
15	sybsystemprocs	sysprocsdev	data and log	204800	156030	76.18

 

\	dbname	data size(KB)	data free(KB)	data free(%)
1	sybsystemdb	2048	766	37.40
2	model	2048	830	40.52
3	sybsystemprocs	204800	156030	76.18
4	master	61440	52706	85.78
5	keno	2048000	1893436	92.45
6	lottery	2048000	1951968	95.31
7	open_database	204800	199472	97.39
8	tempdb	310272	307818	99.20