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.
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] ASdeclare @TableName varchar (100)set nocount onCREATE TABLE #TableRows ( [TableName] [varchar] (100) , [TableRows] [int] NULL )declare rsCount cursor for select [name] from [sysobjects] where xtype ='U'Open rsCountfetch next from rsCount into @TableNamewhile @@fetch_status = 0 beginexec ('insert into #TableRows (TableName,TableRows) select ''' + @TableName + ''' , count (*) from ' + @TableName )fetch next from rsCount into @TableNameendselect * from #TableRowsclose rsCountdeallocate rsCountGOThe 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=15531You 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. |
|
|
|
|
|
|
|