SQL Server Row Count & Find The Allocation For All Tables In A Database

Problem
I am a database consultant and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target database. How do I get the row counts from all the tables in a SQL Server Database? What are the different approaches to get this information?

Solution
It is a common step in any ETL project to validate the row counts between source and target databases as part of the testing phase. Getting the row count from each table one by one and comparing and consolidating the results can be a tedious task. Hence any script/solution which can get the row count information from all the tables in a database can be really helpful and effective thereby considerably reducing the effort involved. In this tip we will see four different approaches to get the row counts from all the tables in a SQL Server database


How to find space and count rows of every table in database
I try to implement some of the faster ways for counting row number rather than the COUNT() that I'm using right now. I am not able to get the result I'm expecting.

I have table Sales. [Customer] and I take the number of the records inside the table simply by:

SELECT COUNT(*) as Row_count FROM Sales.[Customer]
And the result is:

Row_count
19820

Now I'm trying to count the records from the all tables getting use of the sys.partitions

SELECT
s.Name AS Schema_Name,
t.Name AS Table_Name,
p.rows AS Row_Counts,
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 AND p.index_id IN ( 0, 1 )
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
 

sys.partitions Contains a row for each partition of all the tables and most types of indexes in the database.Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.
 

index_id Indicates the ID of the index within the object to which this partition belongs.

For a single table use WHERE [Tables].name = N'name of the table'

Schema_Name Table_Name Row_Counts Used_MB Unused_MB Total_MB
Production WorkOrder 72591 6.59 0.25 6.84
Production WorkOrderRouting 67131 7.19 0.23 7.41
Purchasing ProductVendor 460 0.1 0 0.1
Purchasing PurchaseOrderDetail 8845 0.76 0.02 0.77
Purchasing PurchaseOrderHeader 4012 0.54 0.12 0.66
Purchasing ShipMethod 5 0.05 0 0.05
Purchasing Vendor 104 0.05 0 0.05
Sales CountryRegionCurrency 109 0.03 0 0.03
Sales CreditCard 19118 2.5 0.14 2.64
Sales Currency 105 0.03 0 0.03
Sales CurrencyRate 13532 1.3 0.1 1.4
Sales Customer 19820 2.48 0.3 2.78
Sales PersonCreditCard 19118 0.53 0.1 0.63
Sales SalesOrderDetail 121317 17.02 0.27 17.29
Sales SalesOrderHeader 31465 8.7 0.49 9.19
Sales SalesOrderHeaderSalesReason 27647 0.76 0.06 0.82
Sales SalesPerson 17 0.03 0 0.03
Sales SalesPersonQuotaHistory 163 0.05 0 0.05
Sales SalesReason 10 0.02 0 0.02
Sales SalesTaxRate 29 0.05 0 0.05