Friday, August 31, 2012

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.

1 commentaires:

Pooja said...

Nice post on SQL database...very useful i am currently learning it. I found many other posts in this blog very useful thanks...



sql training

Post a Comment