Blog Post

SQL to find table space used

Jul 05, 2021 SQL by Andrew Thomas

Often times I want to find out which tables are the largest, or have grown over time. The script below I use a lot to for this purpose, and it can be tweaked to sort by whatever field is most useful. Hope you find it useful!

CREATE TABLE #tblResults
(
   [name]   nvarchar(50),
   [rows]   int,
   [reserved]   varchar(50),
   [reserved_int]   int default(0),
   [data]   varchar(50),
   [data_int]   int default(0),
   [index_size]   varchar(50),
   [index_size_int]   int default(0),
   [unused]   varchar(50),
   [unused_int]   int default(0)
)

EXEC sp_MSforeachtable @command1=
         "INSERT INTO #tblResults
           ([name],[rows],[reserved],[data],[index_size],[unused])
          EXEC sp_spaceused '?'"
   
-- Strip out the " KB" portion from the fields
UPDATE #tblResults SET
   [reserved_int] = CAST(SUBSTRING([reserved], 1, 
                             CHARINDEX(' ', [reserved])) AS int),
   [data_int] = CAST(SUBSTRING([data], 1, 
                             CHARINDEX(' ', [data])) AS int),
   [index_size_int] = CAST(SUBSTRING([index_size], 1, 
                             CHARINDEX(' ', [index_size])) AS int),
   [unused_int] = CAST(SUBSTRING([unused], 1, 
                             CHARINDEX(' ', [unused])) AS int)
   
SELECT * FROM #tblResults

DROP TABLE #tblResults

Tags: SQL