Database Sizes

/***************************************************************************************************

        Script Name        :- spDBsize 

        Purpose            :- Show the all databses size.

***************************************************************************************************/






DECLARE @sysdb BIT     -- 1 for include sysdatabases ,0 for Not include sysdatabases

DECLARE @order int -- set the order of columns

DECLARE @orderby bit -- 0 for asc, 1 for desc 





set nocount on 

SET @sysdb = 0

SET @order = 1

SET @orderby = 1

 

declare @id int 

,@type character(2) 

,@pages bigint 

,@dbname sysname 

,@dbsize bigint 

,@logsize bigint 

,@reservedpages bigint 

,@sqlstring varchar(2000) 



create table #spacetemp ( 

id int identity(1,1) 

,DBname varchar(500) 

,DBcreationDate datetime 

,DBsize decimal(10,2) 

,Avispace decimal(10,2)

,DBStatusid bigint

,DBStatus varchar(200)

) 

 

create table #stemp ( 

sid int identity(1,1) 

,mdfpath varchar(200) 

,ldfpath varchar(200) 

,ds bigint 

,dl bigint 

) 

 



insert into #spacetemp (DBname,DBcreationDate,DBStatusid,DBStatus) 

select [name],crdate, status,case

when status=1 then 'autoclose'

when status=4 then 'select into/bulkcopy'

when status=8 then 'trunc'

when status=16 then 'torn page detection'

when status=32 then 'loading'

when status=64 then 'pre recovery'

when status=128 then 'recovering'

when status=256 then 'not recovered'

when status=512 then 'offline'

when status=1024 then 'read only'

when status=2048 then 'dbo use only'

when status=4096 then 'single user'

when status=32768 then 'emergency mode'

when status=4194304 then 'autoshrink'

when status=1073741824 then 'cleanly shutdown'

end

from master..sysdatabases where status!=512 and dbid >case when @sysdb=1 then 0 else 4 end





 

Declare @sDBname varchar(500) 

Declare @sDBcreationDate datetime 

Declare @counter int 

Declare @Maxid int 

 

set @counter = 1 

select @maxid = max(id) from #spacetemp 

 

 



 

 

while (@counter<=@maxid) 

begin 

 

set @id=0 

set @type ='' 

set @pages = 0 

set @dbsize = 0 

set @logsize = 0 

set @reservedpages = 0 

set @sqlstring = '' 

 

select @dbname = DBname from #spacetemp where id =@counter 

 

 

IF @type = 'SQ' 

 SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue 

 

 

--set @sqlstring= 'update #spacetemp set ds = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 

-- ,dl = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from #spacetemp join '+@dbname+'..sysfiles as ss 

--on #spacetemp.dbname=ss.name' 

 

 

set @sqlstring= 'insert into #stemp (ds,dl) 

select sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 

 ,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from ['+@dbname+']..sysfiles' 

 

exec (@sqlstring) 

 

select @dbsize=ds,@logsize=dl from #stemp where sid=@counter 

 

-- select @reservedpages = sum(a.total_pages), 

-- @pages = sum( 

-- CASE 

-- When it.internal_type IN (202,204) Then 0 

-- When a.type <> 1 Then a.used_pages 

-- When p.index_id < 2 Then a.data_pages 

-- Else 0 

-- END 

-- ) 

-- from partitions p join sys.allocation_units a on p.partition_id = a.container_id 

-- left join sys.internal_tables it on p.object_id = it.object_id 

 

update #spacetemp set 

dbsize = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 

 * 8192 / 1048576,15,2)), 

Avispace = ltrim(str((case when @dbsize >= @reservedpages then 

 (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 

 * 8192 / 1048576 else 0 end),15,2)) 

 where [DBNAME]=@DBNAME 

 

--select @dbname,@counter,ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 

-- * 8192 / 1048576,15,2)+' MB'), 

--ltrim(str((case when @dbsize >= @reservedpages then 

-- (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 

-- * 8192 / 1048576 else 0 end),15,2)+' MB'),@dbsize,@logsize 

 

 

set @counter = @counter+1 

 

end 



set @sqlstring='

select 

[DBname] as ''Database Name'' 

,[dbcreationdate] as ''Creation Date'' 

,convert(varchar(200),[dbsize])+'' MB'' as ''size'' 

--,convert(varchar(200),[avispace])+'' MB'' as ''Aviable Space''

--,convert(varchar(200),[dbsize]+[avispace])+'' MB'' as ''Total''

--,[DBstatus] as ''Database Status'' 

--,DBStatusid 

from #spacetemp order by '+case @order

when 1 then 'DBname'

when 2 then 'dbcreationdate'

when 3 then 'dbsize'

--when 4 then 'avispace'

--when 5 then 'dbsize+avispace'

else 'DBname' end+' '+case @orderby when 1 then 'asc' when 0 then 'desc'end +''



exec (@sqlstring)



 

 

drop table #spacetemp 

drop table #stemp