Siusic Dot Com

The other day I was asked to provide a list of database server and all the databases on them with the disk space each database used.

I was looking for an easy way to do this instead of opening up the property page of each database in management studio and looking at the size. First I found this store procedure sp_spaceused. 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.

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

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.

Related Posts:

List All Permissions a User Has in SQL Server Database and Error 4064

How to Restore a Database Used By Web Site 24/7?

Force T-SQL Query to Use Certain Index to Improve Query Performance

Query to Display Tables with Row Counts

MySQL Bugs