| Author |
 Topic  |
|
|
henrikop
Constraint Violating Yak Guru
Netherlands
273 Posts |
 Posted - 05/09/2002 : 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
Netherlands
273 Posts |
Posted - 05/09/2002 : 07:35:11
|
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. |
 |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 05/09/2002 : 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
Netherlands
273 Posts |
Posted - 05/09/2002 : 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. |
|
|
| |
Topic |
|
How to kill this Cursor
