Syndicated/ Tips and Tricks

Determine Space Consumption At The Table Level

0 Comment

A while back, I was asked about how much space certain tables were consuming in one of the databases I support. The following query returns that information for the current database. Keep in mind that the values are returned in 8Kb page values, to convert to megabytes use the following formula: value * 8 / 1024.

–This query returns the results as 8kb pages:
DECLARE @TableName VARCHAR(100)
DECLARE @Results TABLE (name VARCHAR(100), rows bigint, reserved VARCHAR(50), data VARCHAR(50), index_size VARCHAR(50), unused VARCHAR(50))

DECLARE curTables CURSOR FOR
SELECT TABLE_CATALOG + ‘.’ + TABLE_SCHEMA + ‘.’ + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
ORDER BY TABLE_NAME

OPEN curTables
FETCH NEXT FROM curTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Results([name], [rows], [reserved], [data], [index_size], [unused])
EXEC sp_spaceused @TableName, false
FETCH NEXT FROM curTables INTO @TableName
END

CLOSE curTables
DEALLOCATE curTables

SELECT * FROM @Results ORDER BY [reserved] DESC