Database size

use master

declare @PageSize varchar(10)
select @PageSize=v.low/1024.0
from master..spt_values v
where v.number=1 and v.type=’E’

select name as DatabaseName, convert(float,null) as Size
into #tem
From sysdatabases where dbid>4

declare @SQL varchar (8000)
set @SQL=”

while exists (select * from #tem where size is null)
begin
select @SQL=’update #tem set size=(select round(sum(size)*’+@PageSize+’/1024,0) From ‘+quotename(databasename)+’.dbo.sysfiles) where databasename=”’+databasename+””
from #tem
where size is null
exec (@SQL)
end

select * from #tem order by DatabaseName
drop table #tem

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s