T-sql query to find the biggest table in a database with a clustered index

Question

Hi, anyone has a t-sql query to find the biggest table in a database with a clustered index?

or list of tables ordered by large to small , only including tables which have a clustered index

Thanks

Answer 1

Hi @SQLRocker ,

Welcome back to Microsoft Q&A!

Please refer below query and check whether it is helpful to you.

SELECT   
    '[' + (OBJECT_SCHEMA_NAME(tables.object_id,db_id())   
	+ '].[' + tables.NAME + ']') AS TableName,  
    (sum(allocation_units.total_pages) * 8) / 1024 as TotalSpaceMB  
FROM   
    sys.tables tables  
INNER JOIN        
    sys.indexes indexes ON tables.OBJECT_ID = indexes.object_id  
INNER JOIN   
    sys.partitions partitions ON indexes.object_id = partitions.OBJECT_ID  
		 AND indexes.index_id = partitions.index_id  
INNER JOIN   
    sys.allocation_units allocation_units ON partitions.partition_id = allocation_units.container_id  
WHERE     
    indexes.index_id = 1  
GROUP BY   
    tables.object_id,tables.NAME, indexes.object_id, indexes.index_id, indexes.name   
ORDER BY   
    TotalSpaceMB desc  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

SELECT TOP (1) object_name(id), * FROM sysindexes WHERE indid = 1 ORDER BY reserved DESC

While sysindexes is an old deprecated compatibility view, it is very handy for this purpose.

indid = 1 means "is clustered index". Furthermore, the value reserved includes the space occupied by the non-clustered indexes. The values is number of 8K pages.

Answer 3

Thanks @MelissaMa-MSFT , that's exactly what i wanted!

Thanks @Erland Sommarskog too for the response & for your time - However i don't see the table name info from the query, np though - Melissa's query works perfectly.

Answer 4

Thanks @Erland Sommarskog , i made a minor change there:

SELECT TOP (5) object_name(id) as TableName, reserved/128 as Reserved_Size_MB FROM sysindexes WHERE indid = 1 ORDER BY reserved DESC

Pretty much what i am ultimately after is to find the space needed to rebuild a clustered index online, sort_in_tempdb.

So trying to find what should be the sufficient log drive size & tempdb drive size to support a rebuild with the above options, thanks.