Cursor with COUNT(*) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor with COUNT(*)

Author

Topic

Bernard_za
Starting Member

6 Posts

Posted - 2003-03-04 : 03:13:12

I am trying to perform a COUNT(*) result into a table that should hold the TABLE_NAME and TABLE_COUNT for each table that the cursor picks up on.

The result should look like this:
---------------------------------
TABLE_NAME | TABLE_COUNT
---------------------------------
tbl_Table1 | 1250
tbl_Table2 | 450
tbl_Table3 | 1050
tbl_Table4 | 2505

I write a query as follows:
---------------------------
DECLARE
@SQL_CURSOR CURSOR
DECLARE CUR_SOR CURSOR FOR SELECT name FROM sysobjects WHERE name LIKE 'tbl_%' ORDER BY name
SET
@SQL_CURSOR = CUR_SOR
OPEN
CUR_SOR
FETCH NEXT FROM CUR_SOR
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE
@STATEMENT VARCHAR(100)
SET
@STATEMENT = 'SELECT COUNT(*) FROM ' + @SQL_CURSOR
EXEC
(@STATEMENT)
FETCH NEXT FROM CUR_SOR
END
CLOSE CUR_SOR
DEALLOCATE CUR_SOR

The error I get is as follows:
------------------------------
Server: Msg 206, Level 16, State 2, Line 14
Operand type clash: varchar is incompatible with cursor
Server: Msg 403, Level 16, State 1, Line 14
Invalid operator for data type. Operator equals add, type equals cursor.

Please Help!

macka
Posting Yak Master

162 Posts

Posted - 2003-03-04 : 04:20:58

Slight modification to the query in [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14624[/url] gives you:

SELECT so.name as TABLE_NAME,si.rows as TABLE_COUNT
FROM sysindexes si
INNER JOIN sysobjects so ON si.id = so.id
WHERE si.indid in (0,1)
AND so.type = 'U'
AND so.name like 'tbl_%'
ORDER BY so.name

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Bernard_za
Starting Member

6 Posts

Posted - 2003-03-04 : 04:37:51

Thanks! This works just great! 101010101010101010101010101010

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources