http://www.sqlservercentral.com/blogs/sql-blog-by-rahul-sahay/2013/04/03/total-number-of-records-in-each-table/

Printed 2014/09/21 06:21AM

Total Number Of Records In each Table

By rahulsahay123, 2013/04/03

Quite often in our day today activity we need to check the count of records in each table of the database. So instead of firing count(*) against all the table, we can use the DMV's ie inbuild system procedures or functions to get the desired result.

Code :


select  distinct s.name asName_Schema,o.name as Table_Name,p.row_count asTotal_Records from sys.objects o inner join
sys.dm_db_partition_stats p on o.object_id = p.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
where o.type = 'U'


So lets say we want to get the list of tables along with the total records in Adventureworks database.

Below is the output for the same:
 
Name-Schema Table_Name Total_Records
HumanResources Department 16
HumanResources Employee 290
HumanResources EmployeeAddress 290
HumanResources EmployeeDepartmentHistory 296
HumanResources EmployeePayHistory 316
HumanResources JobCandidate 13
HumanResources Shift 3

 

 
 




Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.