Printed 2015/11/27 08:43AM

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 asName_Schema, 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-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.