How to Check the Size and Row Counts of Your Heap Tables in SQL Server

As your SQL Server databases grow in size and complexity, it becomes increasingly important to monitor the performance and resource utilization of your tables. One key metric to track is the size of your tables and the number of rows they contain. In this blog post, we will show you how to check the size and row counts of your SQL Server tables using a simple T-SQL query.

Step 1: Open SQL Server Management Studio

To get started, open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

Step 2: Open a New Query Window

Once you are connected to your SQL Server instance, open a new query window by clicking on the "New Query" button in the toolbar or by pressing the "Ctrl + N" keyboard shortcut.

Step 3: Copy and Paste the T-SQL Query

Copy and paste the following T-SQL query into your query window:

SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS TableName, SUM(p.rows) AS RowCounts, CONVERT(decimal(18,2), SUM(a.total_pages) * 8 / 1024.0) AS TableSizeMB FROM sys.objects o INNER JOIN sys.partitions p ON o.object_id = p.object_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE o.type_desc = 'USER_TABLE' AND o.name NOT LIKE 'sys%' AND o.object_id NOT IN ( SELECT i.object_id FROM sys.indexes i WHERE i.type_desc <> 'HEAP' ) GROUP BY o.schema_id, o.name order by TableSizeMB desc

This query retrieves the schema name, table name, row count, and table size in megabytes (MB) for all user tables in your SQL Server instance. It also excludes system tables and tables that do not have an index.

Step 4: Execute the Query

Once you have copied and pasted the query, click on the "Execute" button in the toolbar or press the "F5" keyboard shortcut to execute the query.

Step 5: View the Results