List Data Size for SQL Tables

To identify which table is taking up the most physical storage space in the database, you may run the following SQL query to return the number of rows and physical sizes of each user defined table in the database.


DECLARE @TableName VARCHAR(100)

-- declare a cursor to get all user table names
DECLARE t_csr CURSOR
FOR 
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
FOR READ ONLY

-- a temp table to store the results
CREATE TABLE #TMP
(
 TableName varchar(100),
 RecordCount int,
 ReservedSize varchar(50),
 DataSize varchar(50),
 IndexSize varchar(50),
 UnusedSize varchar(50)
)

-- open the cursor
OPEN t_csr

-- get the first table name from the cursor
FETCH NEXT FROM t_csr INTO @TableName

-- loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    -- dump the results of the sp_spaceused query to the temp table
    INSERT #TMP
      EXEC sp_spaceused @TableName

    -- get the next table name
    FETCH NEXT FROM t_csr INTO @TableName
END

-- dispose the cursor
CLOSE t_csr
DEALLOCATE t_csr

-- return the stored results by table DataSize
SELECT * 
FROM #TMP
ORDER BY CAST(LEFT(DataSize,LEN(DataSize)-3) AS NUMERIC(18,0)) DESC 

-- clean up
DROP TABLE #TMP