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

Question

question

T-sql query to find the biggest tabular array 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 modest , only including tables which have a clustered index

Thank you

sql-server-general sql-server-transact-sql

Answer 1

Hi @SQLRocker,

Welcome back to Microsoft Q&A!

Please refer below query and check whether information technology is helpful to y'all.

                                      SELECT       '[' + (OBJECT_SCHEMA_NAME(tables.object_id,db_id())       + '].[' + tables.NAME + ']') AS TableName,      (sum(allocation_units.total_pages) * 8) / 1024 equally 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 = one  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 Reply" and upvote information technology.
Note: Please follow the steps in our documentation to enable electronic mail notifications if you want to receive the related electronic mail notification for this thread.

Answer 2

                    SELECT Acme (ane) object_name(id), * FROM sysindexes WHERE indid = i ORDER BY reserved DESC

While sysindexes is an quondam 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.

BTW, this may result in incorrect results (or data repeated) for partitioned tables.

0 Votes 0 ·

Equally it happens I wrote this more modern query yesterday to return table sizes, This one should exist OK with partitioned tables, although I had no reason to business organisation myself with them at the fourth dimension.

                        ; WITH CTE Every bit (    SELECT s.name Equally schema_name, o.name AS object_name, i.index_id,             isnull(a2.type_desc, a1.type_desc) As alloc_type,             isnull(a2.total_pages, a1.total_pages) * 8192 / meg AS total_MB,             isnull(a2.used_pages, a1.used_pages) * 8192 / 1000000 As used_MB    FROM   sys.schemas s    JOIN   sys.objects o ON s.schema_id = o.schema_id    JOIN   sys.indexes i ON o.object_id = i.object_id    JOIN   sys.partitions p ON i.object_id = p.object_id                            AND i.index_id = p.index_id    LEFT   JOIN sys.allocation_units a1 ON a1.container_id = p.hobt_id                                        AND a1.type IN (1,iii)    LEFT   JOIN sys.allocation_units a2 ON a2.container_id = p.partition_id                                        AND a2.type = 2 ) SELECT schema_name, object_name, alloc_type, COUNT(Distinct index_id),        SUM(total_MB) As total_MB, SUM(used_MB) AS used_MB FROM   CTE  Grouping  BY schema_name, object_name, alloc_type ORDER By total_MB DESC

i Vote 1 ·

Answer 3

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

Thank you @ErlandSommarskog too for the response & for your time - Still i don't see the tabular array name info from the query, np though - Melissa's query works perfectly.

Thanks @ErlandSommarskog likewise for the response & for your fourth dimension - However i don't see the table proper name info from the query, np though

Oops! I was a petty too quick in that location. I've edited my mail to include the object name. I've too added DESC for the Gild By clause, which most shamefully was misisng.

Melissa'south query is more than kosher, since it uses the modern catalog views. Note, nevertheless, that here query only gives y'all the size of the data pages, but it does non include the not-amassed indexes which mine does.

0 Votes 0 ·

Answer 4

Thank you @ErlandSommarskog , i made a minor change there:

SELECT TOP (v) object_name(id) every bit TableName, reserved/128 as Reserved_Size_MB FROM sysindexes WHERE indid = one Order Past 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 discover what should exist the sufficient log bulldoze size & tempdb drive size to back up a rebuild with the above options, thanks.

How To Find Largest Tables In Sql Server

question