SQL Server: Displaying the sizes of your database's tables

For maintenance purpose, I needed to create a dashboard that reports database tables’ size to monitor growth of them. The first command that should be known is the sp_spaceused stored procedure. This command returns the following information:

·         Name (table name)

·         Rows (number of rows existing)

·         Reserved (Total amount of reserved space)

·         Data (Total amount of space used by data)

·         Index_size (Total amount of space used by indexes)

·         Unused (Total amount of space reserved but not yet used)

EXEC sp_spaceused 'dbo.Countries'

Good start J, but I would need to have this command ran for every tables. First idea is to lookup on sys.objects table (also with sys.schemas for schema name) using a cursor and run the sp_spaceused stored procedure for each table.

This could be done as follow:

DECLARE @tableName VARCHAR(255)

DECLARE table_cursor CURSOR FOR

SELECT  sys.schemas.name + '.' + sys.objects.name AS TableName

FROM    sys.objects

INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

WHERE sys.objects.type = 'U'

ORDER BY TableName

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @tableName

WHILE @@FETCH_STATUS = 0

BEGIN

    EXEC sp_spaceused @tableName

    FETCH NEXT FROM table_cursor INTO @tableName

    END

CLOSE table_cursor

DEALLOCATE table_cursor

Result set is

Now a better (smarter) solution: perform such action using the undocumented Stored Procedure sp_MSforeachtable.

Command is: EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'", where “?” represents table name during the “for each” function. The result is exactly the same as using the cursor (Except the sorting by table name)

This Stored Procedure can also be used in  a query like the following one:

CREATE TABLE #tableCount (TableName VARCHAR(255), RowNb INT)

EXEC sp_MSforeachtable 'INSERT INTO #tableCount SELECT ''?'', COUNT(*) FROM ?'

SELECT * FROM #tableCount

DROP TABLE #tableCount

That’s a very simple way to query tables’ size; you can then create a simple web application that displays this information.

To go further with undocumented Stored Procedure, you can also play with sp_MSforeachdb, that query databases on the server.