Technical Article

Finding rows count in a table without Select...

,

 

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.

 

 

Create Proc Sp_RowCount( @Tname as varchar(100)) As
Declare @SQL Varchar(500)
Set @SQL='Select Object_Name(Id) As [Table Name], Rows From sysindexes Where indid < 2 And Id=' + Str(Object_Id(@Tname)) 
Exec (@SQL)
Go

EXEC SP_MSFOREACHTABLE @Command1="Exec SP_ROWCOUNT '?'"

Or

Create Table #Tmp
(
[table name] varchar(255),
Rows int
)

Insert into #Tmp 
Exec SP_MSFOREACHTABLE @COMMAND1="EXEC SP_ROWCOUNT '?'" 
Select * From #Tmp

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.38 (16)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.38 (16)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,597 reads