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

SQLRocker 126 Reputation points
2021-06-02T19:09:26.933+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,620 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-03T01:57:51.607+00:00

    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.

    3 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-06-02T21:57:28.327+00:00 
    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.

    1 person found this answer helpful.
  2. SQLRocker 126 Reputation points
    2021-06-03T16:56:24.107+00:00

    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.

  3. SQLRocker 126 Reputation points
    2021-06-04T20:59:03.697+00:00

    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.

    0 comments