Below query can be used to find top 10 largest tables in SQL Server database.

use​​​​ <DBNAME>​​ -- replace your​​ database​​ name here


select​​ ​​ ​​​​ top​​​​ 10​​ @@servername​​ as​​ SQLServer,​​ db_name()​​ as​​ "Database",

st.Name​​​​ as​​​​ TableName,

ss.Name​​​​ as​​​​ SchemaName,

sp.rows​​​​ as​​​​ RowCounts,

cast(sum(a.used_pages​​​​ *​​​​ 8)/1024.00​​​​ as​​​​ numeric(36,​​​​ 2))​​​​ as​​​​ Used_MB,

cast(sum(a.total_pages​​​​ *​​​​ 8)/1024.00​​​​ as​​​​ numeric(36,​​​​ 2))​​​​ as​​​​ Total_MB

from​​​​ sys.tables​​​​ st

inner​​​​ join​​​​ sys.indexes​​​​ i​​​​ on​​​​ st.OBJECT_ID​​​​ =​​​​ i.object_id

inner​​​​ join​​​​ sys.partitions​​​​ sp​​​​ on​​​​ i.object_id​​​​ =​​​​ sp.OBJECT_ID​​​​ and​​​​ i.index_id​​​​ =​​​​ sp.index_id

inner​​​​ join​​​​ sys.allocation_units​​​​ a​​​​ on​​​​ sp.partition_id​​​​ =​​​​ a.container_id

inner​​​​ join​​​​ sys.schemas​​​​ ss​​​​ on​​​​ st.schema_id​​​​ =​​​​ ss.schema_id

group​​​​ by​​​​ st.Name,​​​​ ss.Name,​​​​ sp.Rows

order​​​​ by​​​​ sum(a.used_pages)​​​​ desc


Sample Output:

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.