Determine All SQL Server Table Sizes

Apr 27 2010 6:35 AM

I’m doing some work to migrate and optimize a large-ish (40GB) SQL Server database at the moment.  Moving such a database between data centers over the Internet is not without its challenges.  In my case, virtually all of the size of the database is the result of one table, which has over 200M rows of data.  To determine the size of this table on disk, you can run the sp_TableSize stored procedure, like so:

EXEC sp_spaceused lq_ActivityLog

This results in the following:

image

Of course this is only showing one table – if you have a lot of tables and need to know which ones are taking up the most space, it would be nice if you could run a query to list all of the tables, perhaps ordered by the space they’re taking up.  Thanks to Mitchel Sellers (and Gregg Stark’s CURSOR template) and a tiny bit of my own edits, now you can!  Create the stored procedure below and call it to see a listing of all user tables in your database, ordered by their reserved space.

-- Lists Space Used for all user tables
CREATE PROCEDURE GetAllTableSizes
AS
DECLARE @TableName VARCHAR(100)

DECLARE tableCursor CURSOR FORWARD_ONLY
FOR 
select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

OPEN tableCursor

WHILE (1=1)
BEGIN
    FETCH NEXT FROM tableCursor INTO @TableName
    IF(@@FETCH_STATUS<>0)
      BREAK;

    INSERT  #TempTable
        EXEC sp_spaceused @TableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

UPDATE #TempTable
SET reservedSize = REPLACE(reservedSize, ' KB', '')

SELECT tableName 'Table Name',
numberofRows 'Total Rows',
reservedSize 'Reserved KB',
dataSize 'Data Size',
indexSize 'Index Size',
unusedSize 'Unused Size'
FROM #TempTable
ORDER BY CONVERT(bigint,reservedSize) DESC

DROP TABLE #TempTable

GO

Running this results in something like the following (confirming that in my case, lq_ActivityLog is in fact the largest table in my database):
image
 
Look for another post soon that details some of the steps I’m taking to reduce the size of this table (and its related Summary tables, shown above).

Alternately, assuming you have access, just go into Object Explorer and right click on your database, then select Reports – Standard Reports – Disk Usage by Top Tables.  You’ll get something very similar (and a bit prettier):

image

3 Comments

  • ssmith said

    SQL guru Penton also recommended this via twitter: <a target="_blank" href="http://davidpenton.com/testsite/scratch/sp_TABLE_INFORMATION.txt">davidpenton.com/.../sp_TABLE_INFORM</a>

  • Andrzej said

    Hi Steven,
    Small statement for many different schemas in database (not only dbo).
    -- Lists Space Used for all user tables
    CREATE PROCEDURE GetAllTableSizes
    AS
    DECLARE @TableName VARCHAR(200)
    DECLARE tableCursor CURSOR FORWARD_ONLY
    FOR
    --&lt; TableName with SchemaName &gt;
    SELECT
    sys.schemas.name + '.' + sys.tables.name
    FROM
    sys.tables INNER JOIN sys.schemas
    ON sys.tables.schema_id = sys.schemas.schema_id
    WHERE
    sys.tables.schema_id &gt; 1
    ORDER BY
    sys.schemas.schema_id
    FOR READ ONLY
    CREATE TABLE #TempTable
    (
    tableName varchar(200),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
    )
    OPEN tableCursor
    WHILE (1=1)
    BEGIN
    FETCH NEXT FROM tableCursor INTO @TableName
    IF(@@FETCH_STATUS&lt;&gt;0)
    BREAK;
    INSERT #TempTable
    EXEC sp_spaceused @TableName
    END
    CLOSE tableCursor
    DEALLOCATE tableCursor
    UPDATE #TempTable
    SET reservedSize = REPLACE(reservedSize, ' KB', '')
    SELECT
    tableName 'Table Name',
    numberofRows 'Total Rows',
    reservedSize 'Reserved KB',
    dataSize 'Data Size',
    indexSize 'Index Size',
    unusedSize 'Unused Size'
    FROM #TempTable
    ORDER BY CONVERT(bigint,reservedSize) DESC
    DROP TABLE #TempTable
    GO

Add a Comment