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

@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

• Anonymous
  March 21, 2009
  That's a useful script, Suhas !! Good to see you "Blogging"....will look forward to many useful post from you. Varun

• Anonymous
  April 13, 2009
  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

• Anonymous
  April 13, 2009
  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?

• Anonymous
  May 12, 2009
  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?

• Anonymous
  May 12, 2009
  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.

• Anonymous
  February 27, 2012
  Suhas De and Raj Kaimal, thank you for teh simple code :)