0Problem
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 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 same table getting use of the sys.partitions
SELECT SCHEMA_NAME(schema_id) AS [Schema_Name],
[Tables].name AS [Table_Name],
SUM([Partitions].[rows]) AS [Total_Rowcount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
GROUP BY SCHEMA_NAME(schema_id), [Tables].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.
- 0 = heap
- 1 = clustered index
- 2 or greater = nonclustered index
For a single table use WHERE [Tables].name = N'name of the table'
Schema_Name Table_Name Total_Rowcount
Person Address 19614
Person AddressType 6
Production BillOfMaterials 2679
Person BusinessEntity 20777
Person BusinessEntityAddress 19614
Person BusinessEntityContact 909
Person ContactType 20
Person CountryRegion 238
Sales CountryRegionCurrency 109
Sales Customer 19820
