How to find sizes of all user tables in a database

Hi Friends!

Recently I was asked to write a query that would us us know the sizes of all user tables in a database. Thought of sharing the script; this might be useful to people who would like to know the amount of space being used by each of the tables in a particular database. Here goes the script:

DECLARE @TableFullName VARCHAR(257)

  TableName NVARCHAR(257
  NumberOfRows BIGINT
  ReservedSpace NVARCHAR(20
  DataSpace NVARCHAR(20
  IndexSpace NVARCHAR(20
  UnusedSpace NVARCHAR(20

SELECT [O].[name], [S].
  FROM [dbo].[sysobjects] [O] (nolock),
[sys].[schemas] [S] (nolock)
  WHERE [O].[xtype] =
  AND [O].[uid] = [S].

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName,
WHILE (@@Fetch_Status >= 0
    SET @TableFullName = @SchemaName + ‘.’ +
    INSERT #TempTable EXEC sp_spaceused
    UPDATE #TempTable SET TableName = @TableFullName
WHERE TableName =
    FETCH NEXT FROM tableCursor INTO @TableName,

SELECT * FROM #TempTable 
ORDER BY CONVERT(BIGINT,LEFT(ReservedSpace, LEN(ReservedSpace)-3))
TABLE #TempTable

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided “AS IS” with no warranties and confers no rights.

Comments (6)

  1. Varun says:

    That’s a useful script, Suhas !!

    Good to see you "Blogging"….will look forward to many useful post from you.


  2. Raj Kaimal says:

    How about this?

    DECLARE @TableVar  TABLE


       Name NVARCHAR(50),

       NumberOfRows INT,

       ReservedSpace NVARCHAR(20),

       DataSpace  NVARCHAR(20),

       IndexSize  NVARCHAR(20),

       Unused  NVARCHAR(20)


    INSERT INTO @TableVar

         EXEC sp_msforeachtable ‘sp_spaceused ”?”’

    SELECT * FROM @TableVar

    But why write a script when we can use the built in SQL report – "Disk usage by table"?


    Raj Kaimal

  3. Suhas De says:

    Hi Raj,

    Thanks for the comments. The script written by you is perfect for user databases; try running both scripts against the master or the msdb database, you will see the difference.

    The reason why I had written this script is – my customer needed to have a SQL job, that would email the sizes of all user tables of a particular database to a particular operator. I guess thats where you would really need the script, right?

  4. prams_great says:

    I want to create a new Database on my production.

    I want to know how to determine the initial Database size.

    Can you pls suggest?

  5. Suhas De says:

    Hi prams_great,

    There is no direct way to get an initial database size directly. You will have to do it on a per table basis, and then sum up to get the total database size.

    For a table, since you know the table structure, you should be able to easily determine the number of bytes required to hold 1 row of data. Each data page in SQL Server can hold 8060 bytes of data, so divide 8060 by the size of each row, and you will have the number of rows per page. Take the integer value only.

    Now, you will need to estimate the number of rows in the table. Divide the estimated number of rows by the number of rows per page, and you have the number of pages required to hold the data for that table. Multiply that by 8KB and you have the space required for storing data in that table in KB.

    You will need to, similarly, estimate space requirement for the indexes on that table. Sum these up, and you have the total space requirement for the table. To be on the safe side, add a 20% buffer.

    Follow the above steps for all the other tables, and finally sum the values up. This is the space required for the database.

    Hope this helps.

  6. shradg says:

    Suhas De and Raj Kaimal, thank you for teh simple code 🙂