How to kill this Cursor - 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

SQL Server Development (2000)

How to kill this Cursor

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-05-09 : 07:02:41

As an extra tool for some users I made a webform to get some statistics of the database, namely the number of records each table has.

I wrote this procedure :

CREATE PROCEDURE [dbo].[spCountRecords] AS
declare @TableName varchar (100)

set nocount on

CREATE TABLE #TableRows (
[TableName] [varchar] (100) ,
[TableRows] [int] NULL )

declare rsCount cursor for select [name] from [sysobjects] where xtype ='U'
Open rsCount
fetch next from rsCount into @TableName

while @@fetch_status = 0
begin

exec ('insert into #TableRows (TableName,TableRows) select ''' + @TableName + ''' , count (*) from ' + @TableName )
fetch next from rsCount into @TableName

end

select * from #TableRows

close rsCount
deallocate rsCount
GO

The result is exactly what I want. However, as Nigel (nr --> where is he nowadays?) stated, cursors are for people who don't know SQL, I want to get rid of this cursor.

How can I write this procedure without the cursor??? It's fun for me, and I have no performance issues and I don't get killed if I can't get rid of the cursor, so only use your spare time to get me to the next level of SQL programming.

Thx!

BTW here's the result of this procedure on the pubs database.

TableName TableRows
titleauthor 25
stores 6
sales 21
roysched 86
discounts 3
jobs 14
pub_info 8
employee 43
dtproperties 0
authors 23
publishers 8
titles 18

Henri

~~~
SQL is nothing, writing it everything.

Edited by - henrikop on 05/09/2002 07:11:22

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-05-09 : 07:35:11

Talking about nr... I saw he wrote this procedure already:

[url]http://home.btclick.com/NIGELRIVETT/sp_GetRowsForAllTables.html[/url]

However, I try to get rid of writing cursors.... Are there any articles with examples of cursor-procedures translated to non-cursor procedures?

Henri

~~~
SQL is nothing, writing it everything.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-09 : 08:36:54

Ummmm, where do you see a cursor in nr's code? I don't even see the word "cursor" in there.

This might help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15531

You can get the number of rows in all tables as a single SELECT statement. You can also tweak it to accept parameters that limit which tables you're interested in.

If you do a forum search on SQL Team (not a regular search) for "cursor" you'll find COUNTLESS examples of cursor code that was converted to set-base SQL operations.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-05-09 : 08:49:19

Wow!!

SELECT O.name, I.rowcnt
FROM sysindexes I INNER JOIN sysobjects O on I.id=O.id
WHERE I.indid < 2 AND O.xtype='U'

is brilliant!!!

And yes, Nigel didn't use 'cursor'. I checked some topics with 'CURSOR' in them, but it was hard to find the right trees in this big forest

Henri

~~~
SQL is nothing, writing it everything.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources