Matthew Pavey | Faith, Family, Code

SQL Server has a handy system stored procedure called sp_spaceused that reports the space used by a database or by an individual table. To see it for the whole database:

EXEC sp_spaceused

That returns two result sets. The first has the database name, its size, and unallocated space. The second breaks the size down into how much is reserved, how much of that is data, how much is indexes, and how much is unused.

To look at a single table, pass the table name as the first parameter:

EXEC sp_spaceused 'Orders'

That gives you one result set containing:

• Name, the name of the table
• Rows, the number of rows in the table
• Reserved, total reserved space for the table
• Data, space used by the data
• Index_Size, space used by the table's indexes
• Unused, unused space in the table

Usually what you actually want is this for every table at once, which means running sp_spaceused once per table. You could query the system catalog for a list of tables and iterate with a cursor. The easier route is sp_MSforeachtable, an undocumented stored procedure that takes a command and runs it against every user table in the database. Put a question mark where you want the table name substituted:

EXEC sp_MSforeachtable @command1 = "EXEC sp_spaceused '?'"

That runs EXEC sp_spaceused 'TableName' for each user table, which works but gives you one result set per table. To get it all back as a single result set, create a temp table, let sp_MSforeachtable insert into it, and select from it at the end:

CREATE TABLE #spaceused
(
    name       VARCHAR(128),
    rows       BIGINT,
    reserved   VARCHAR(25),
    data       VARCHAR(25),
    index_size VARCHAR(25),
    unused     VARCHAR(25)
)

EXEC sp_MSforeachtable @command1 = "INSERT INTO #spaceused EXEC sp_spaceused '?'"

SELECT * FROM #spaceused

DROP TABLE #spaceused

One caution worth stating: sp_MSforeachtable is undocumented, which means Microsoft has never committed to keeping it or to its behavior staying the same. It has been there a long time and it is widely used, but it is not something to build production code around. For an ad hoc look at where your space is going, it is hard to beat.