This is a very nice system store procedure that can give you the disk usage information of a database, a table or an index.
But when you use it to get database size you have to execute it within the database in question. It doesn’t take a database name as parameter. So it can be automated to run for each database using T-SQL. What I want is query that is able to list all database on the server together with the size they take up. So I wrote the following query to do that. It worked fine for me.
declare @PageSize varchar(10)
from master..spt_values v
where v.number=1 and v.type='E'
select name as DatabaseName, convert(float,null) as Size
From sysdatabases where dbid>4
declare @SQL varchar (8000)
while exists (select * from #tem where size is null)
select @SQL='update #tem set size=(select round(sum(size)*'+@PageSize+'/1024,0) From '+quotename(databasename)+'.dbo.sysfiles) where databasename='''+databasename+''''
where size is null
select * from #tem order by DatabaseName
drop table #tem
The first select statement is to get how many kilobytes a data page has. SQL Server allocates disk space in the unit of data page. Currently each SQL server data page contains 8k bytes. The number of data pages allocated to each database file is recorded in the sysfiles system table. With this information on hand the script creates a temporary table #tem and update the temporary table with size information which is gathered by querying the sysfiles table.