Many times we need to find out how many rows a particular table has?. Generally Select Count(*) From
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 "
" and pressing F5 instead of writing Select Count(*) From ""
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
No comments:
Post a Comment