Determining SQL Table Size

You may come across a situation where you need to shrink your database by selectively deleting data from some tables.

It sure would be nice to know which tables are the biggest data hog and see if you can delete records to free up some space and then shrink the database. There is indeed a nice little script that you can use to determine the table size and act accordingly.

CREATE TABLE #tmpTable
(name SYSNAME, 
rows CHAR(11), 
reserved VARCHAR(18),  
data VARCHAR(18), 
index_size VARCHAR(18), 
unused VARCHAR(18)
)  
EXEC sp_msforeachtable 'INSERT INTO #tmpTable EXEC sp_spaceused ''?''' 
SELECT * FROM #tmpTable ORDER BY data desc 
DROP TABLE #tmpTable 

If you are running SQL 2008 (or SQL 2012 enterprise edition, then you can also compress individual tables. We will discuss table compression in next post.

Thank you