Platinum Partner

SQL Server - Get Table Size Data

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
{{ tag }}, {{tag}},

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

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}