How to Show Tables by Size?

  • Once you have connected to MS SQL using SSMS
  • Right click on the database
  • Select New Query
  • Paste the below query and click Execute
 SELECT     t .NAME AS TableName,     s .Name AS SchemaName,     p .rows AS RowCounts,     SUM(a .total_pages) * 8 AS TotalSpaceKB,     SUM(a .used_pages) * 8 AS UsedSpaceKB,     (SUM( a.total_pages ) - SUM( a.used_pages )) * 8 AS UnusedSpaceKB 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 LEFT OUTER JOIN     sys.schemas s ON t.schema_id = s .schema_id WHERE     t .NAME NOT LIKE 'dt%'     AND t. is_ms_shipped = 0     AND i. OBJECT_ID > 255 GROUP BY     t .Name, s.Name , p. Rows ORDER BY     t .Name 
Results may vary. Below is what will appear after you execute the query.

Content retrieved from:

Updated on November 11, 2019

Was this article helpful?

Related Articles

Need Support?
Can't find the answer you're looking for? Don't worry we're here to help!