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

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