SQL

How to get size of all tables in database using SQL Query?

How to get size of all tables in database using SQL Query?, someone asked me to explain?
SQL

In this article, I will show you how much space used/unused by the all tables in SQL server database using SQL Query.

Here, I listed all the tables in a database with column table name, schema name, counts of row the table, total space consumed (KB) , total space unconsumed(KB).

If you run the following SQL Query you will get size of all tables in Database.

SQL QUERY:

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.is_ms_shipped= 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

 

OUTPUT:

sql-check size of table in database using SQL Query

Post your comments / questions