SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Finding rows count in a table without Select...

By Manish Mittal,


Many times we need to find out how many rows a particular table has?. Generally Select Count(*) From <Table Name> query is used for same. It becomes very time consuming if a table is having bulk records. Here is an another way to find the rows count in table very quickly no matter how many millions rows that table is having:

Refer to SQL Script from row No: 1 to 4

Run the above script n the master database this way SP Sp_RowCount will be available in your all databases which exists in server.

Now just type SP_ROWCOUNT "<table name>" and pressing F5 instead of writing Select Count(*) From "<table name>"


Use it in more efficient way if you work more with SQL Query Analyzer:
Go to Query Analyzer ? Tools ? Customize... and link the Sp_RowCount with any short cut what like. Click on Apply and close the dialog box. Now close this SQL Query Analyzer instance and open new one. Type any table name in SQL Query Analyzer window and and double click on the table name what you typed (to select it) and press the short cut keys combination what you defined in Customize dialog box. It will return you total rows count for the selected table name.

Finding rows count for each table in a database:

Refer to SQL Script from row No: 7 to 19


1. Sysindex table contain the rows count of each table.

2. Indid column is ID of index:
1 = Clustered index
>1 = Nonclustered
255 = Entry for tables that have text or image data
3. If a SP is having "SP_" prefix and run in "master" database, it is available in rest of the databases too.



Total article views: 4730 | Views in the last 30 days: 5
Related Articles

select count

select count


Select count(*)

Today I was reading(I always read Paul Randal). http://www.sqlskills.com/BLOGS/PAUL/post/Which-in...


Query Analyzer Extended

SQL Server has the best client tools for a DBA of any RDBMS and SQL Server 2000 includes Query Analy...


Analyzing Data Storage - Ten SHOWCONTIG Queries

Analyzing the data storage in SQL Server databases is a bit of an art. It's a skill that takes some ...


Starting Query Analyzer

Next in his series on Query Analyzer, Yakov Shlafman brings us a few ways that we can save more keys...