SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to kill this Cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 05/09/2002 :  07:02:41  Show Profile  Visit henrikop's Homepage  Reply with Quote
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

Netherlands
280 Posts

Posted - 05/09/2002 :  07:35:11  Show Profile  Visit henrikop's Homepage  Reply with Quote
Talking about nr... I saw he wrote this procedure already:

http://home.btclick.com/NIGELRIVETT/sp_GetRowsForAllTables.html

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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 05/09/2002 :  08:36:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 05/09/2002 :  08:49:19  Show Profile  Visit henrikop's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000