This approach can be used for testing purposes but it is not recommendedįor use in any production code.sp_MSforeachtable is an undocumented system stored procedure.Individual tables, puts them together and displays the results for all the tables. This is an iterative approach which captures the row count for each of the.Through each of the tables to capture the row count for all the tables in a database.ĭECLARE TABLE ( VARCHAR(128), INT) ĮXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?' The T-SQL query below uses the sp_MSforeachtable system stored procedure to iterate The row counts from each of the tables in a given database in an iterative fashionĪnd display the record counts for all the tables at once. To iterate through each of the tables in a database. Sp_MSforeachtable is an undocumented system stored procedure which can be used Approach 3: sp_MSforeachtable System Stored Procedure Management View/Function which is being queried.
VIEW SERVER STATE or VIEW DATABASE STATE permissions based on the In general, querying the Dynamic Management Views (DMVs), requires Or when an object/partition is dropped and recreated. The values in the sys.dm_db_partition_stats DMV are reset on server restart.VIEW DATABASE STATE permissions are required in the database.INNER JOIN sys.dm_db_partition_stats AS sdmvPTNSīelow are the highlights of this approach: QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS The T-SQL query below uses the sys.partitions catalog view to capture the row Every table in SQL Server contains at least one partition (default partition)Įven if the table is not explicitly partitioned. Of each of the tables and most types of indexes (Except Fulltext, Spatial, and XML Sys.partitions is an Object Catalog View and contains one row for each partition sp_MSforeachtable System Stored Procedure.sys.dm_db_partition_stats Dynamic Management View (DMV).Let's take a look at each of the approaches: To get the row counts from all the tables in a SQL Server database. In this tip we will see four different approaches Hence any script/solution which can get the row count informationįrom all the tables in a database can be really helpful and effective thereby considerably
To validate the row counts between source and target databases as part of theĬount from each table one by one and comparing and consolidating the results canīe a tedious task.