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.

Leave a Reply