How To Retrieve SQL Server Database Table Size

Today, while working on my data warehouse performance tuning and cleanup activity, I wanted to identify the large space hungry tables in my database so that they can be compressed to save some space. I was aware of system stored procedure sp_spaceused which can be used to retrieve space usage details of individual tables. I came up with this little script to list the space details of all tables in my database so that this information can be shared in a report.

---------------------------------------------------------

-- Author : Irfan Sheikh (http://iirfan.blogspot.com/)

-- Date : 10/01/2012

-- Description :  Script to list space used by individual database tables

---------------------------------------------------------

USE [DatabaseName]

GO

-- Declare table variable to store table space information

DECLARE @tabSpace TABLE

(

name varchar(50),

[rows] decimal,

reseved varchar(50),

data varchar(50),

index_size varchar(50),

unused varchar(50)

)

-- Declare table variable to store table names

DECLARE @Tabs TABLE (ID INT IDENTITY(1,1), TableName VARCHAR(50))

-- Populate list of tables in current DB

INSERT INTO @Tabs (TableName)

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE @Ctr INT

DECLARE @TabName VARCHAR(100)

-- Initialize counter for Looping

SELECT @Ctr = MIN(ID) FROM @Tabs

SELECT @TabName = TableName FROM @Tabs WHERE ID = @Ctr

WHILE (@TabName IS NOT NULL)

BEGIN

      -- Populate output of sp_spaceused in Table variable

      INSERT INTO @tabSpace

      EXEC sp_spaceused @TabName

     

      SET @Ctr = @Ctr + 1

      SET @TabName = NULL

      SELECT @TabName = TableName FROM @Tabs WHERE ID = @Ctr

     

END

-- Final Select

SELECT * FROM @tabSpace ORDER BY CAST(REPLACE(DATA,'KB','') AS DECIMAL) DESC

Here is sample output of this script:

name	rows	reseved	data	index_size	unused
DatabaseLog	1591	6672 KB	6528 KB	72 KB	72 KB
spt_values	2506	336 KB	152 KB	152 KB	32 KB
spt_monitor	1	16 KB	8 KB	8 KB	0 KB
MSreplication_options	3	16 KB	8 KB	8 KB	0 KB
ProductCostHistory	0	0 KB	0 KB	0 KB	0 KB
ProductDescription	0	0 KB	0 KB	0 KB	0 KB
ShoppingCartItem	0	0 KB	0 KB	0 KB	0 KB