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)