Determine the Space Used by Each Table in a SQL Server Database

The following script will work on SQL Server 2005 and 2008 (and 2012?).  Use it to determine a number of metrics about each table in a database, including number of rows, data size, index size, unused space, and total space reserved for the table.

SET NOCOUNT ON

DECLARE @table_name VARCHAR(500)

DECLARE @SpaceUsed TABLE (    
    tablename sysname,
    row_count INT,
    reserved VARCHAR(50),
    data VARCHAR(50),
    index_size VARCHAR(50),
    unused VARCHAR(50)
)

DECLARE curTables CURSOR FOR 
SELECT s.name + ‘.’ + t.name  
FROM sys.tables t  INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

OPEN curTables
FETCH NEXT FROM curTables INTO @table_name
WHILE @@FETCH_STATUS = 0 
BEGIN  
    SET @table_name = REPLACE(REPLACE(@table_name, ‘[',''), ']‘, ”)

    — make sure the object exists before calling sp_spacedused
    IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
    BEGIN
        INSERT INTO @SpaceUsed EXEC sp_spaceused @table_name, false
    END
    FETCH NEXT FROM curTables INTO @table_name
END
CLOSE curTables
DEALLOCATE curTables

– Compute the total size of each table and add the schemaname to the result set
SELECT    s.name as schemaname,
        su.tablename,
        su.row_count,
        su.data,
        su.index_size,
        su.unused,
        CONVERT(decimal, CONVERT(bigint, REPLACE(su.data, ‘ KB’, ”)) +
            CONVERT(bigint, REPLACE(su.index_size, ‘ KB’, ”)) +
            CONVERT(bigint, REPLACE(su.unused, ‘ KB’, ”))) / 1000 AS [total (MB)]
FROM    @SpaceUsed su INNER JOIN sys.tables t
            ON t.name = su.tablename
        INNER JOIN sys.schemas s
            ON t.schema_id = s.schema_id
ORDER BY 
        [total (MB)] DESC,
        schemaname,
        tablename

 

Here is an example of the output you can expect from this script:

schemaname tablename     row_count   data      index_size unused   total (MB)
———- ————- ———– ——— ———- ——– ———-
dbo        Page          116213      71864 KB  66304 KB   344 KB   138.512000
dbo        PageName      164508      17040 KB  37520 KB   336 KB   54.896000
dbo        IndicatedPage 103127      6208 KB   32 KB      40 KB    6.280000
dbo        Page_PageType 115770      4840 KB   32 KB      0 KB     4.872000
dbo        Item          254         136 KB    144 KB     48 KB    0.328000
dbo        Title         77          64 KB     224 KB     0 KB     0.288000