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.
 

Add Feedback