Simple Cursor to Select Tables by Prefix and Creation Date

Cursor to Select Tables by Prefix and Creation Date

The cursor query below runs against a database and finds every table with a specific prefix (‘b_’, ‘delete_’). It also checks whether the table is older than a certain number of days, or was created before a certain date, and drops it. We could just as easily have this run some other operation on the table instead, like a delete, a print, or an index rebuild.

SET NOCOUNT ON

DECLARE @lcl_name VARCHAR(100)

DECLARE cur_name CURSOR FOR

SELECT name

FROM sysobjects

WHERE type = 'U'

AND crdate <= DATEADD(m,-1,GETDATE())

AND name LIKE 'b_%'

OPEN cur_name

FETCH NEXT FROM cur_name INTO @lcl_name

WHILE @@Fetch_status = 0

BEGIN

SELECT @lcl_name = 'sp_depends' +@lcl_name

PRINT @lcl_name

-- EXEC (@lcl_name)

FETCH NEXT FROM cur_name INTO @lcl_name

END

CLOSE cur_name

DEALLOCATE cur_name

SET NOCOUNT OFF

Here are a few points worth remembering about cursors.

• Cursors are nothing more than loops, since they rely on WHILE loops internally.
• Overusing cursors can hurt query performance, since they can consume a lot of resources.
• Instead of using a cursor to insert one row at a time, it’s better to use set-based operations like SELECT…INSERT or INSERT INTO…SELECT.