DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

SQL Server - Get Table Size Data

05.13.2009
| 5846 views |
  • submit to reddit
        Grabs table size data of the current database and outputs it into a temp table with proper integer data types for ordering.

DECLARE @tblTempSizes TABLE(
      [name] varchar(100),
      [rows] integer, 
      [reserved] varchar(100),
      [data ]varchar(100),
      [index_size] varchar(100),
      [unused] varchar(100) )

DECLARE @names TABLE([name] varchar(100))
INSERT INTO @names
SELECT name FROM sys.TABLEs where type = 'u'

DECLARE @counter CURSOR
DECLARE @tempString VARCHAR(100)

SET @counter = CURSOR
FOR
SELECT [name] FROM @names

OPEN @counter

FETCH NEXT FROM @counter INTO @tempString

WHILE @@FETCH_STATUS = 0
BEGIN
      INSERT INTO @tblTempSizes exec sp_spaceused @tempString
FETCH NEXT FROM @counter INTO @tempString
END

DEALLOCATE @counter

DECLARE @tblSizes TABLE(
      [name] varchar(100),
      [rows] integer,
      [reserved] integer,
      [data] integer,
      [index_size] integer,
      [unused] integer )
INSERT INTO @tblSizes
SELECT [name],rows,replace(reserved,' KB','') as [reserved(KB)], replace(data,' KB','') as [data(KB)], replace(index_size,' KB','') as [index_size(KB)], replace(unused,' KB','') as [unused(KB)] 
FROM @tblTempSizes

SELECT *
FROM @tblSizes
ORDER BY data DESC