Click here to Skip to main content
15,398,348 members
Articles / Database Development
Tip/Trick
Posted 12 Feb 2010

Stats

52.2K views
20 bookmarked

Record Count of Tables in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.86/5 (22 votes)
16 Aug 2011CPOL
The below query can be used to get the record count of all tables in the current database.

The query below can be used to get the record count of all the tables in the current database.


Code


SQL
SELECT 
T.TABLE_NAME AS [TABLE NAME], MAX(I.ROWS) AS [RECORD COUNT]
FROM SYSINDEXES I, INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME = OBJECT_NAME(I.ID)
      AND T.TABLE_TYPE = 'BASE TABLE'
GROUP BY T.TABLE_SCHEMA, T.TABLE_NAME

Result


Example output against Northwind database:



TABLE NAMERECORD COUNT
Categories8
CustomerCustomerDemo0
CustomerDemographics0
Customers91
Employees9
EmployeeTerritories49
Order Details2155
Orders830
Products97
Region4
Shippers3
Suppliers29
Territories53

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

thatraja
Team Leader
India India
No Biography provided

Comments and Discussions

 
GeneralReason for my vote of 5 Nice one.. My 5 Pin
Tejas Vaishnav13-Nov-11 20:58
professionalTejas Vaishnav13-Nov-11 20:58 
GeneralReason for my vote of 5 good code to understand Pin
sriram from Hyderabad16-Aug-11 2:01
Membersriram from Hyderabad16-Aug-11 2:01 
GeneralReason for my vote of 4 Nice query Pin
Doncp19-Jul-10 10:51
MemberDoncp19-Jul-10 10:51 
GeneralTo Mr.Md. Marufuzzaman Pin
thatraja19-Feb-10 17:28
professionalthatraja19-Feb-10 17:28 
JokeThanks man Pin
Angsuman Chakraborty14-Feb-10 22:10
MemberAngsuman Chakraborty14-Feb-10 22:10 
GeneralRe: Thanks man Pin
thatraja15-Feb-10 5:59
professionalthatraja15-Feb-10 5:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.