Davis Exter1987

  • Home
  • Sitemap



banner



Home  ›  How To Find Largest Tables In Sql Server

How To Find Largest Tables In Sql Server

Written By Nadeau Appidint96 Friday, April 15, 2022 Add Comment Edit

question

SQLRocker avatar image

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

MelissaMa-msft avatar image

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.


ErlandSommarskog avatar image

                    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 ·

SQLRocker avatar image

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 ·

SQLRocker avatar image

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.

question details

Related Questions

How To Find Largest Tables In Sql Server,

Source: https://docs.microsoft.com/answers/questions/419756/t-sql-query-to-find-the-biggest-table-in-a-databas.html

Posted by: davisexter1987.blogspot.com

Share this post

0 Response to "How To Find Largest Tables In Sql Server"

Post a Comment

Newer Post Older Post Home
Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel

Popular Post

  • How To Find Locus Of A Point
    How To Find Locus Of A Point
  • Atlético De Madrid - 15 manualidades para hacer con niños en Navidad 2020 : De beers' high jewelry necklace is featured in town & country's jewel of the day.
    Atlético De Madrid - 15 manualidades para hacer con niños en Navidad 2020 : De beers' high jewelry necklace is featured in town & country's jewel of the day.
  • Armenia Flag - You, Me, & Sicily! by Eszter Vajda — Kickstarter - It showed lucy standing with charlie brown and she's got a look of complete joy on her face as she shouts every item on this page was chosen by a woman's day ed.
    Armenia Flag - You, Me, & Sicily! by Eszter Vajda — Kickstarter - It showed lucy standing with charlie brown and she's got a look of complete joy on her face as she shouts every item on this page was chosen by a woman's day ed.
  • How To Find Out Where My Husband Is
    How To Find Out Where My Husband Is
Copyright 2021 Davis Exter1987