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
USE [DATABASE_NAME]
GO
--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
INSERT INTO #tmpSizes
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
select
[name],
replace([rows],'KB',''),
replace(reserved,'KB',''),
replace(data,'KB',''),
replace(index_size,'KB',''),
replace(unused,'KB','')
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: