SQL SERVER – Simple Query to List Size of the Table with Row Counts

Sometimes we need simple query which can do the task, then complicated solution. Here is a simple query which list size of the table in MB with Row Counts. Below is a Simple Query to List Size of the Table with Row Counts

USE [YourDBName] -- replace your dbname
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
Code language: PHP (php)

When you run above query, it usually runs in just a split seconds and it also does not lock any of your essential tables while it is running (only shared lock for select statement), hence this query is very much alright to run on your production database. Trust me, this may be simple but very powerful script you must keep in your bookmark.