This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script for finding the largest object of a SQL Server Database.
Whenever I find any handy script which we can use in day to day work, I am sharing without fail.
When you are doing the database clean up or maintenance activity, you probably need to check the big size of objects.
I already shared few couples of the script on checking the size of table, database, log files.
SQL Server: Script to find the size of Database Files and Log Files
Here, below script is used for finding the largest object of a database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT 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 FROM 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 WHERE 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 SUM(a.total_pages) DESC |
Leave a Reply