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.

Thanks in advance.

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) 

Answer 2

Use sp_spacedUsed

Exec sp_spaceused N'YourTableName'

Or if you want to execute the sp_spaceused for each table in your database then you can use this SQL:

set nocount on
create table #spaceused (
  name nvarchar(120),
  rows char(11),
  reserved varchar(18),
  data varchar(18),
  index_size varchar(18),
  unused varchar(18)
)

declare Tables cursor for
  select name
  from sysobjects where type='U'
  order by name asc

OPEN Tables
DECLARE @table varchar(128)

FETCH NEXT FROM Tables INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN
  insert into #spaceused exec sp_spaceused @table
  FETCH NEXT FROM Tables INTO @table
END

CLOSE Tables
DEALLOCATE Tables 

select * from #spaceused
drop table #spaceused

exec sp_spaceused

The above SQL is from here