quote:

---

Originally posted by robvolk

If all you need is a raw count of all the rows in a table, here's a shortcut that works as a stored procedure and requires no dynamic SQL:

CREATE PROCEDURE RowCount @table varchar(128) AS
SET NOCOUNT ON
SELECT @table TableName, rows FROM sysindexes WHERE id=object_id(@table) AND indid<2

Usage: exec RowCount 'myTable'

An additional benefit is that this procedure doesn't actually query the table and is the fastest way to get the total row count.

---

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'RowCount'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@table".

quote:

---

Originally posted by Yellowdog

quote:

---

Originally posted by robvolk

If all you need is a raw count of all the rows in a table, here's a shortcut that works as a stored procedure and requires no dynamic SQL:

CREATE PROCEDURE RowCount (@table varchar(128)) AS
SET NOCOUNT ON
SELECT @table TableName, rows FROM sysindexes WHERE id=object_id(@table) AND indid<2

Usage: exec RowCount 'myTable'

An additional benefit is that this procedure doesn't actually query the table and is the fastest way to get the total row count.

---

I was sure there was a way that I was not aware of and I thank you for this but I cannot create the procedure

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'RowCount'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@table".

not sure what the problem is here...
RowCount comes up as a keyword, am I supposed to put the name of the procedure here or is that correct?
I am guessing that the second error is a result of the first but again I am still trying to get a handle on this.

As it is now I have set up a basic if statement in a procedure that gets passed a table name and It simply runs a select count statement based on the table name but this seems less than efficient.

Thanks again for all your help

---

CREATE PROCEDURE MyRowCount (@table varchar(128)) AS
SET NOCOUNT ON
SELECT @table TableName, rows FROM sysindexes WHERE id=object_id(@table) AND indid<2

Usage: exec MyRowCount 'myTable'

 SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@download_status_id", 6);
SqlParameter[] sales = new SqlParameter[1];
sales[0] = new SqlParameter("@varTable", "downloaded_sales_data");
SqlParameter[] service = new SqlParameter[1]; 
service[0] = new SqlParameter("@varTable","downloaded_service_data");

quote:

---

RowCount is reserved

---

quote:

---

Originally posted by robvolk

quote:

---

RowCount is reserved

---

I knew that! I swear!

Funny part is I spent quite a bit of time creating a server policy in SQL 2008 to prevent us from naming objects after reserved words.

---

CREATE PROCEDURE [RowCount] ...

quote:

---

Originally posted by GilaMonster

That's the way to do it.

--
Gail Shaw
SQL Server MVP

---