How To Find Tables With Large Amounts Of Data In SQL Server



TODO:

You want to find tables that contain large amounts of data, which can be due to large row counts.  The solution will get table stats, and sort by row count descending.

 

SOLUTION:

SELECT 
    t.NAME AS 'Table',
    i.name as 'Index',
    sum(p.rows) as 'Rows',
    sum(a.total_pages) as 'Total Pages', 
    sum(a.used_pages) as 'Used Pages', 
    sum(a.data_pages) as 'Data Pages',
    (sum(a.total_pages) * 8) / 1024 as 'Total Space (MB)', 
    (sum(a.used_pages) * 8) / 1024 as 'Used Space (MB)', 
    (sum(a.data_pages) * 8) / 1024 as 'Data Space (MB)'
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 
    Rows DESC

 

NOTES:

There are no notes on this topic.



Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading