How to find sizes of all user tables in a database

  • Article

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.