Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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

Note:

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: 4684 | Views in the last 30 days: 4
 
Related Articles
FORUM

select count

select count

BLOG

Select count(*)

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

ARTICLE

Query Analyzer Extended

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

ARTICLE

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 ...

ARTICLE

Starting Query Analyzer

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

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones