SQL Server database space usage

Ever wanted to know what the biggest table in your database is, and what the other space hogs are? There is the sp_spaceused system stored procedure that either shows headline stats for the database as a whole, or those for a particular table. Annoyingly it doesn’t tell you which table is taking up all the space relative to all the others. If you have SQL Server SP2 installed then it ups SQL Management Studio

So, here’s a T-SQL batch that shows all the space taken up by tables in the current database. The main column to look at is “Total size”, which is a sum of the Data size, Index size and Unused columns.

SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
CREATE TABLE #spaceused
(
    Name nvarchar(255),
    Rows int,
    [Total size] varchar(50),
    [Data size] varchar(50),
    [Index size] varchar(50),
    Unused varchar(50)
)
DECLARE @table_name nvarchar(255) 

DECLARE tables_cursor CURSOR LOCAL FORWARD_ONLY FOR
    SELECT name FROM sysobjects WHERE type='U'
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #spaceused EXEC sp_spaceused @table_name
    FETCH NEXT FROM tables_cursor INTO @table_name
END

SELECT * FROM #spaceused
    ORDER BY CONVERT(int, LEFT([Total size],
                     CHARINDEX(' KB', [Total size]))) DESC 

DROP TABLE #spaceused
CLOSE tables_cursor
DEALLOCATE tables_cursor

Of note is the very useful INSERT INTO table EXEC proc technique for capturing the output of a stored proc.

The script works on SQL Server 2000 and 2005 (and possibly later).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s