Kill the 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 2005 Forums

Transact-SQL (2005)

Kill the cursor

Author

Topic

davidc
Starting Member

26 Posts

Posted - 2009-09-29 : 07:42:06

I have a database in which I have created a stored procedure. The SP creates a number of output tables which may have 0 or many rows. At the end of the procedure the code creates a cursor which contains the names of the output tables by selecting tables names from the master db sysobjects. I use the cursor to pull in each table, check for empty table and drop those which are empty.

Is there a set based alternative ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 07:44:41

yup...there might be...but for suggesting alternative we need more info...can you give us an overview of what you're trying to achieve with some sample data?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-29 : 07:46:51

[code]DECLARE @sql varchar(max)
SET @sql=''
SELECT @sql=@sql + 'DROP TABLE ' + quotename(o.name) + ';'
FROM sysobjects o
INNER JOIN sysindexes i ON o.id=i.id AND i.indid<2
WHERE i.rows=0
print(@sql) -- change print to exec if you want to actually drop the tables[/code]

davidc
Starting Member

26 Posts

Posted - 2009-09-29 : 07:54:35

This is what happens now:

DECLARE TABLE_CURSOR CURSOR LOCAL FOR
SELECT DISTINCT TABLNAME as TABLENAME
FROM DWC_TEST.[DBO].[JK_DB_TABLES]
WHERE DBNAME = @lcDatabaseName
ORDER BY TABLENAME
OPEN TABLE_CURSOR
FETCH NEXT FROM TABLE_CURSOR INTO @lcTable
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @lcSqlCmd =
'IF (SELECT COUNT (*) FROM ' + RTRIM(@lcDatabaseName) + '.[DBO].[' +@lcTable+'] ) = 0 ' + @lcCR +
'BEGIN ' + @lcCR +
'DROP TABLE [' + RTRIM(@lcDatabaseName) + '].[DBO].[' +@lcTable+'] ' + @lcCR +
'INSERT INTO ' + RTRIM(@lcDatabaseName) + '.dbo.JK_DUPES_AUDIT ' + @lcCR +
'SELECT GETDATE(), ''Deleted ' +@lcTable+ ' - empty table '' ' + @lcCR +
'END'
EXEC (@lcSqlCmd)
FETCH NEXT FROM TABLE_CURSOR INTO @lcTable
END
CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-29 : 08:00:32

quote:
DECLARE @sql varchar(max)
SET @sql=''
SELECT @sql=@sql + 'DROP TABLE ' + quotename(o.name) + ';'
FROM sysobjects o
INNER JOIN sysindexes i ON o.id=i.id AND i.indid<2
WHERE i.rows=0
print(@sql) -- change print to exec if you want to actually drop the tables

That will also drop table-valued functions and sys tables,
I changed it a bit to make it safer

DECLARE @sql varchar(max)
SET @sql=''
SELECT @sql=@sql + 'DROP TABLE ' + quotename(o.name) + ';'
FROM sys.objects o
INNER JOIN sysindexes i ON o.object_id=i.id AND i.indid<2
WHERE i.rows=0
and o.type_desc = 'USER_TABLE'
print(@sql)

Jim

Everyday I learn something that somebody else already knew

davidc
Starting Member

26 Posts

Posted - 2009-09-29 : 08:04:23

Jim,

Thanks for your help - magic !!

David

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-29 : 08:06:13

quote:
Don't thank me, thank Rob, my skill set is limited to taking other people's good ideas!

No, THANK YOU JIM! I tend to miss little things like that, and that helps explain why I get TVFs in some scripts where I don't expect them.

robvolk edit: Dammit! I thought I was replying to you but I hit the edit button accidentally.

I think I'm gonna stop posting for the rest of the day.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:06:49

Also dont forget to apply DBCC Updateusage(0) with COUNT_ROWS before querying the sysindexes table to get proper row count

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources