Getting the Disk Usage Statistics for all Tables in a Database

I know, there are standard reports and other GUI tools for doing this task for SQL Server 2005 or later but what about when you need to extract such information from a SQL Server 2000 instance or when you just want to write some code? :)

The undocumented stored procedure sp_msforeachtable is here to help you (along with the stored procedure sp_spaceused)!

Here's how:

--Select the database to be scanned for table disk usage

--Create temporary table 1 - Sizes will be in strings/KB
create table #tmpSizes(
[name] nvarchar(200),
[rows] varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)

--Create temporary table 1 - Sizes will be in KB
create table #tmpSizesFinalKB(
[name] nvarchar(200),
[rows] int,
reserved int,
data int,
index_size int,
unused int

--Get the disk usage per table and store in temp table 1
EXEC sp_MSforeachtable @command1="sp_spaceused '?'"

--Indirect casting and copying of the information in temp table 2
--This is only when you want to store the disk usage statistics
--in a form that allows sorting operations etc.  
insert into #tmpSizesFinalKB
from #tmpSizes

--Access the disk usage results (in KB)
select * 
from #tmpSizesFinalKB
order by reserved desc

Now you can easily manipulate the disk usage statistics in temp table #tmpSizesFinalKB in order to represent them in the form you may like!

Useful? Please comment/like/tweet!

My Latest Projects:

Labels: , , ,