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 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
Reference: Pinal Dave (http://www.SQLAuthority.com)