Blogs from Suhas

Dirtying my hands in SQL Server

How to find sizes of all user tables in a database

How to find sizes of all user tables in a database

Rate This
  • Comments 6

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 @TableName NVARCHAR(128)
DECLARE @SchemaName VARCHAR(128
)
DECLARE @TableFullName VARCHAR(257)

CREATE TABLE #TempTable
(
  TableName NVARCHAR(257
),
  NumberOfRows BIGINT
,
  ReservedSpace NVARCHAR(20
),
  DataSpace NVARCHAR(20
),
  IndexSpace NVARCHAR(20
),
  UnusedSpace NVARCHAR(20
)
)

DECLARE TableCursor CURSOR FOR
SELECT [O].[name], [S].
[name]
  FROM [dbo].[sysobjects] [O] (nolock),
      
[sys].[schemas] [S] (nolock)
  WHERE [O].[xtype] =
'U'
  AND [O].[uid] = [S].
[schema_id]
FOR READ ONLY

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName,
@SchemaName
WHILE (@@Fetch_Status >= 0
)
  BEGIN
    SET @TableFullName = @SchemaName + '.' +
@TableName
    INSERT #TempTable EXEC sp_spaceused
@TableFullName
    UPDATE #TempTable SET TableName = @TableFullName
      
WHERE TableName =
@TableName
    FETCH NEXT FROM tableCursor INTO @TableName,
@SchemaName
  END
CLOSE
TableCursor
DEALLOCATE TableCursor

SELECT * FROM #TempTable 
  
ORDER BY CONVERT(BIGINT,LEFT(ReservedSpace, LEN(ReservedSpace)-3))
DESC
DROP
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
  • That's a useful script, Suhas !!

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

    Varun

  • 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"?

    Regards,

    Raj Kaimal

  • 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?

  • 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?

  • 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.

  • Suhas De and Raj Kaimal, thank you for teh simple code :)

Page 1 of 1 (6 items)
Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post