question

SQLRocker avatar image
0 Votes"
SQLRocker asked SQLRocker edited

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
2 Votes"
MelissaMa-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog edited
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLRocker avatar image
0 Votes"
SQLRocker answered ErlandSommarskog commented

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

Thanks @ErlandSommarskog 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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @ErlandSommarskog too for the response & for your time - However i don't see the table name info from the query, np though

Oops! I was a little too quick there. I've edited my post to include the object name. I've also added DESC for the ORDER BY clause, which most shamefully was misisng.

Melissa's query is more kosher, since it uses the modern catalog views. Note, however, that here query only gives you the size of the data pages, but it does not include the non-clustered indexes which mine does.


0 Votes 0 ·
SQLRocker avatar image
0 Votes"
SQLRocker answered SQLRocker edited

Thanks @ErlandSommarskog , 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.