Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Server - Get Table Size Data

DZone's Guide to

SQL Server - Get Table Size Data

·
Free Resource
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
Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}