SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created

Following cursor query runs through the database and find all the table with certain prefixed (‘b_’,’delete_’). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other operation on that table like to delete, print or index.

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 of the points one should remember with regards to cursors.

• Cursors are nothing but loops as they use WHILE loops
• Overusing cusrors can negatively impact performance of query as they can use too much of the resources
• It is better to use set theory, operations like SELECT…INSERT or INSERT INTO…SELECT rather than cursor to insert one row at one time.

Please note that this is very old blog post and I used it in my production server at that time and till today I use the same logic in my production server after so many years.

Reference: Pinal Dave (http://www.SQLAuthority.com)