SQL Query to Check Table Size

This Query will help to know Table Size with Lines Count. No need to amend this query simply copy and paste in your local SQL database and run..

Select t.Name AS TableName, p.rows AS RowCounts, CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB, Cast (Round((Sum(a.Total_Pages)- SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB, CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB 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 — INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, p.Rows ORDER BY Total_MB desc