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

Following cursor query runs through 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 opertation on that table like delete, print or reindex.

SET NOCOUNT ON
DECLARE @lcl_name VARCHAR(100)DECLARE cur_name CURSOR FOR
SELECT nameFROM sysobjectsWHERE type = 'U'AND crdate <= DATEADD(m,-1,GETDATE())
AND name LIKE 'b_%'OPEN cur_nameFETCH NEXT FROM cur_name INTO @lcl_nameWHILE @@Fetch_status = 0BEGIN
SELECT @lcl_name = 'sp_depends ' +@lcl_namePRINT @lcl_name--  EXEC (@lcl_name )FETCH NEXT FROM cur_name INTO @lcl_nameEND
CLOSE cur_nameDEALLOCATE cur_nameSET NOCOUNT OFF
Reference: Pinal Dave (http://www.SQLAuthority.com)