Click here to Skip to main content
15,867,453 members
Articles / Database Development
Tip/Trick

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 54.6K   1   20   6
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)


Written By
Team Leader
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

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
sriram from Hyderabad16-Aug-11 2:01 
GeneralReason for my vote of 4 Nice query Pin
Doncp19-Jul-10 10:51
Doncp19-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
Angsuman 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.