Thursday, May 20, 2010

MS SQL 2005 Tip: Display Estimated Sizes of All Tables in a Database

Today I needed to get a quick estimate of tables sizes in an MS SQL 2005 database. So I ended up using the sp_spaceused stored procedure, which displays the space used for either a database or a single object (table, etcetera).

As I was interested in a more detailed listing, I threw together a quick cursor that pulls all table names from the information_schema views, and inserts the results for each item into a table variable.

Updated: Updated to incorporate improvements from comments

USE MyDatabaseName
GO

--- suppress extraneous row counts
SET NOCOUNT ON
GO

---    Temporary table used to store results
DECLARE @spaceUsed TABLE 
(
    RecordID int IDENTITY(1,1),
    TableName nvarchar(128),
    Rows varchar(20),
    Reserved varchar(20),
    Data varchar(20),
    Index_size varchar(20),
    Unused varchar(20),
    SchemaName nvarchar(128)
)


---  get the names of all tables in the current database 
DEClARE @id int
DECLARE @tableName VARCHAR(260)
DECLARE @tableSchema VARCHAR(128)
DECLARE @tables CURSOR
SET @tables = CURSOR FOR SELECT    TABLE_SCHEMA, TABLE_SCHEMA +'.'+ TABLE_NAME 
                         FROM      INFORMATION_SCHEMA.TABLES 
                         WHERE     TABLE_TYPE = 'Base Table'

OPEN @tables 
FETCH NEXT FROM @tables INTO @TableSchema, @TableName
--- For each table ...
WHILE @@FETCH_STATUS = 0 -- lazy check
BEGIN
    BEGIN TRY
        --- insert the estimated size into the work table
        INSERT INTO @spaceUsed (TableName, Rows, Reserved, Data, Index_Size, Unused)
        EXEC sp_spaceused @TableName
        
        --- update the schema information
        UPDATE    @spaceUsed
        SET       SchemaName = @TableSchema
        WHERE     RecordID = SCOPE_IDENTITY()

    END TRY
    BEGIN CATCH
        --- ignore any errors
        PRINT 'Unable to calculate space for table ['+ @TableName +'] '+ char(10) + ERROR_MESSAGE()
    END CATCH

    FETCH NEXT FROM @tables INTO @TableSchema, @TableName
END

CLOSE @tables
DEALLOCATE @tables

Since the resulting sizes are formatted as strings (ie xxx KB), I had to run a quick update to remove the non-numeric values. Then I was able to calculate the total size for each table, and sort the results numerically. So I could get an idea of which were the largest tables in my database.

It is not at all pretty. But it did get the job done.

--- remove KB prefix so values can be cast to numeric type ...
UPDATE @spaceUsed
SET  Reserved  = REPLACE(Reserved, ' KB', '')
    , Data   = REPLACE(Data, ' KB', '')
    , Index_size = REPLACE(Index_size, ' KB', '')
    , Unused  = REPLACE(Unused, ' KB', '')
    , Rows   = REPLACE(Rows, ' KB', '')


--- display estimates for each table (order by largest amount used)
SELECT SchemaName 
    ,  TableName 
    , CAST(Rows AS decimal) AS Rows
    , CAST(Reserved AS decimal) AS ReservedKB
    , CAST(Data AS decimal) AS DataKB
    , CAST(Index_size AS decimal) AS IndexKB
    , CAST(Data AS decimal) + CAST(Index_size AS decimal) AS TotalUsedKB
    , CAST(Unused AS decimal) AS UnusedKB
FROM @spaceUsed
ORDER BY TotalUsedKB DESC

--- display overall estimates for database
EXEC sp_spaceused
GO

SET NOCOUNT OFF
GO

If you are interested in listing the overall sizes for a series of MS SQL datasources, there is good example in an older entry on John Whish's blog.


7 comments:

Dan G. Switzer, II May 21, 2010 at 7:20 AM  

There's also a report built-in to MSSQL 2005 that will generate a list of the diskspace of all tables.

I use the "Disk Usage by Top Tables" report a lot to get an idea of what my table storage requirements look like.

cfSearching May 21, 2010 at 11:33 AM  

Nice one! I will have to check that one out on a machine that has reporting tools installed. (Unfortunately, my current one does not).

-Leigh

Sami Hoda May 24, 2010 at 10:41 AM  

The script doesn't seem to support Schema. So only dbo.X will work, but newSchema.X will not.

cfSearching May 24, 2010 at 12:34 PM  

@Sami,

Yes, you are right. In my case, I only had a single schema to work with. But adding in the table schema is a quick fix. Just concatenate:

TABLE_SCHEMA +'.'+ TABLE_NAME

I will update the entry to include that. Thanks!

-Leigh

Headsplode June 1, 2010 at 4:21 PM  

I'm sure you know this but for your readers, If you're ever interested in doing this in MySQL there's also an easy way to do it.


SELECT table_name, table_rows, data_length, auto_increment
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name like ('tbl%')

This simple query provides the table name, estimated rows, size in bytes and last auto increment.

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep