"how to find largest objects in a sql server database?" Code Answer


i've been using this sql script (which i got from someone, somewhere - can't reconstruct who it came from) for ages and it's helped me quite a bit understanding and determining the size of indices and tables:

    t.name as tablename,
    i.name as indexname,
    sum(p.rows) as rowcounts,
    sum(a.total_pages) as totalpages, 
    sum(a.used_pages) as usedpages, 
    sum(a.data_pages) as datapages,
    (sum(a.total_pages) * 8) / 1024 as totalspacemb, 
    (sum(a.used_pages) * 8) / 1024 as usedspacemb, 
    (sum(a.data_pages) * 8) / 1024 as dataspacemb
    sys.tables t
inner join      
    sys.indexes i on t.object_id = i.object_id
inner join 
    sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join 
    sys.allocation_units a on p.partition_id = a.container_id
    t.name not like 'dt%' and
    i.object_id > 255 and  
    i.index_id <= 1
group by 
    t.name, i.object_id, i.index_id, i.name 
order by 

of course, you can use another ordering criteria, e.g.

order by sum(p.rows) desc

to get the tables with the most rows, or

order by sum(a.total_pages) desc

to get the tables with the most pages (8k blocks) used.

By Juan Cruz Soler on September 7 2022

Answers related to “how to find largest objects in a sql server database?”

Only authorized users can answer the Search term. Please sign in first, or register a free account.