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 @TableSchema VARCHAR(128)
DECLARE @TableName VARCHAR(128)
DECLARE @TableFullName VARCHAR(256)

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

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

DECLARE @MTMP TABLE 
(
 TableFullName varchar(256),
 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 @TableSchema, @TableName

-- loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    SET @TableFullName = @TableSchema + '.' + @TableName
    -- dump the results of the sp_spaceused query to the temp table
    INSERT @MTMP
        EXEC sp_spaceused @TableFullName
	  
		INSERT #TMP
		SELECT @TableSchema, 
		TableFullName,
		RecordCount,
		ReservedSize,
		DataSize,
		IndexSize,
		UnusedSize
		FROM @MTMP

		DELETE FROM @MTMP
		
    -- get the next table name
    FETCH NEXT FROM t_csr INTO @TableSchema, @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