sql server size of every table in a db

Solutions on MaxInterview for sql server size of every table in a db by the best coders in the world

showing results for - "sql server size of every table in a db"
Roberta
10 Oct 2020
1SELECT 
2    t.NAME AS TableName,
3    s.Name AS SchemaName,
4    p.rows,
5    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
6    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
7    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
8    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
9    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
10    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
11FROM 
12    sys.tables t
13INNER JOIN      
14    sys.indexes i ON t.OBJECT_ID = i.object_id
15INNER JOIN 
16    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
17INNER JOIN 
18    sys.allocation_units a ON p.partition_id = a.container_id
19LEFT OUTER JOIN 
20    sys.schemas s ON t.schema_id = s.schema_id
21WHERE 
22    t.NAME NOT LIKE 'dt%' 
23    AND t.is_ms_shipped = 0
24    AND i.OBJECT_ID > 255 
25GROUP BY 
26    t.Name, s.Name, p.Rows
27ORDER BY 
28    TotalSpaceMB DESC, t.Name
29