SQL Server – Query to list table size and row counts

Sometimes we need database usage by its tables and total number of records in each table. By default you will not get table size and total number of records for Microsoft SQL Server database from SQL management studio. However we can obtain those information using simple query.

You can execute the following query to get the table size details as well as number of rows in each table:

USE [YOUR_DB_NAME]
GO
SELECT
s.Name AS SchemaName,
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, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO

Make sure that you replace YOUR_DB_NAME with your actual database name. Once it is executed, you will get the result as shown in the following image: