How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?

Question

How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?

I am sure there is some System Stored Procedure that shows this information.

I have a TEST database that grew from 1tb to 23tb. We are currently doing a lot of client conversion testing in the database, which entails running the same conversion Stored Procedure multiple times. It does DELETEs which I am sure is increasing the Transaction Log. But this got me thinking to ask this question.

info

the big problem is the dbo.Download table, it creates massive storage that is actually not needed, I had 3GB before truncating it, then 52MB ;)

Answer 1

Try this script - it will list the number of rows and the space used by data rows (and the total space used) for all tables in your database:

SELECT   t.NAME AS TableName,  i.name AS indexName,  SUM(p.rows) AS RowCounts,  SUM(a.total_pages) AS TotalPages,   SUM(a.used_pages) AS UsedPages,   SUM(a.data_pages) AS DataPages,  (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,   (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,   (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB FROM   sys.tables t INNER JOIN    sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN   sys.allocation_units a ON p.partition_id = a.container_id WHERE   t.NAME NOT LIKE 'dt%' AND  i.OBJECT_ID > 255 AND    i.index_id <= 1 GROUP BY   t.NAME, i.object_id, i.index_id, i.name  ORDER BY   OBJECT_NAME(i.object_id)