Get size ,row count and used space of all tables in database in SQL Server

Requirement was to find biggest tables and do maintenance for same, This is quick query to find tables with maximum used space followed by row count.

Remember table with huge size need maintenance not with row count only

/******************Row count and space used by Table ****************/

SELECT

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    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

    UsedSpaceMB desc,RowCounts 

I have run this query on “AdventureWorks” database and here is your output