sql server size of every table in a db 1

sql server size of every table in a db

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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 
    TotalSpaceMB DESC, t.Name

Here is what the above code is Doing:
1. It’s joining the sys.tables, sys.indexes, sys.partitions, sys.allocation_units, and sys.schemas tables together.
2. It’s filtering out any tables that start with “dt” (this is a convention I use for my datatype tables) and any tables that are system tables.
3. It’s grouping the results by table name and schema name.
4. It’s ordering the results by the total space used by the table.
5. It’s calculating the total space used by the table, the total space used by the table, and the total space unused by the table.
6. It’s converting the results to MB.

Here’s what the results look like:

TableName SchemaName rows TotalSpaceKB TotalSpaceMB UsedSpaceKB UsedSpaceMB UnusedSpaceKB UnusedSpaceMB

Table1 dbo 1 16 0.02 16 0.02 0 0

Table2 dbo 1 16 0.02 16 0.02 0 0

Table3 dbo 1 16 0.02 16 0.02 0 0

Table4 dbo 1 16 0.02 16 0.02 0 0

Table5 dbo 1 16 0.02 16 0.02 0 0

Table6 dbo 1 16 0.02 16 0.02 0 0

Table7 dbo 1 16 0.02 16 0.02 0 0

Table8 dbo 1 16 0.02 16 0.02 0 0

Table9 dbo 1 16 0.02 16 0.02 0 0

Table10 dbo 1 16 0.02 16 0.02 0 0

Table11 dbo 1 16 0.02 16 0.02 0 0

Table12 dbo 1 16 0.02 16 0.02 0 0

Table13 dbo 1 16 0.02 16 0.02 0 0

Table14 dbo 1 16 0.02 16 0.02 0 0

Table15 dbo 1 16 0.02 16 0.02 0 0

Table16 dbo 1 16 0.02 16 0.02 0 0

Table17 dbo 1 16 0.02 16 0.02 0 0

Table18 dbo 1 16 0.02 16 0.02 0 0

Table19 dbo 1 16 0.02 16 0.02 0 0

Table20 dbo 1 16 0.02 16 0.02 0 0

Table21 dbo 1 16 0.02 16 0.02 0 0

Table22 dbo 1 16 0.02 16 0.02 0 0

Table23 dbo 1 16 0.02 16 0.02 0 0

Table24 dbo 1 16 0.02 16 0.02 0 0

Similar Posts