List Top Tables in SQL Server Database by Size

There are countless queries you can find on internet to get list of tables, size of database, tables, indexes etc... Here is a one that I have used for my needs in my current employment. I am not only interested in size of tables but also size of indexes in each table.

Note: This query relies on metadata/catalog views to get the table size, row count etc.. as SQL Server does a pretty good job of keeping them up to date. However in rare cases if in doubt about the accuracy of the results, you can run the following command in the database to manually correct any inaccuracies. On relatively large database this can take a while to complete so keep that in mind. Generally though you do not need to run DBCC UDPATEUSAGE on regular basis.

DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS

Query to get top 100 tables by size - largest to smallest...

-- DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;
SELECT top 100

    s.Name AS schema_name,
    tbl.NAME AS table_name,

    SUM(SUM(au.total_pages) * 16 /1024) over(partition by ind.object_id) total_table_size_mb, 
    SUM(SUM(au.used_pages) *  16 /1024) over(partition by ind.object_id) total_used_space_mb, 
    ind.name index_name,
    ind.type_desc index_type,
    INDEXPROPERTY(ind.object_id, ind.name, 'IsUnique') is_unique,
    (select count(*) from sys.index_columns ic where ic.object_id = ind.object_id and ic.index_id = ind.index_id and ic.is_included_column = 0) indexed_columns,
    (select count(*) from sys.index_columns ic where ic.object_id = ind.object_id and ic.index_id = ind.index_id and ic.is_included_column = 1) included_columns,
    SUM(p.rows) AS ind_row_count,
    SUM(au.total_pages) * 16 /1024 AS ind_size_mb, 
    SUM(au.used_pages) *  16 /1024 AS ind_used_size_mb, 
    SUM(au.used_pages) *  16 / SUM(p.rows) ind_avg_row_size_kb,
    (SUM(au.total_pages) - SUM(au.used_pages)) * 16 /1024 AS vunused_space_mb,
    INDEXPROPERTY(ind.object_id, ind.name, 'IndexDepth') index_depth,
    ind.index_id
FROM 
    sys.tables tbl
    INNER JOIN sys.indexes ind ON tbl.OBJECT_ID = ind.OBJECT_ID
    INNER JOIN sys.partitions p ON ind.OBJECT_ID = p.OBJECT_ID AND ind.index_id = p.index_id
    INNER JOIN sys.allocation_units au ON p.PARTITION_ID = au.container_id
    INNER JOIN sys.schemas s ON tbl.SCHEMA_ID = s.SCHEMA_ID
-- WHERE tbl.NAME = '<Schema.Table_Name>'
GROUP BY  s.Name, ind.object_id, ind.index_id, tbl.Name, ind.name, ind.type_desc
HAVING SUM(p.rows) > 0
ORDER BY  total_used_space_mb desc

List Top Tables in SQL Server Database by Size

Query to get top 100 tables by size - largest to smallest...

Rate

Share

Share

Rate