Deleting ZZ'd Objects

If you are like me, you have some method or process of checking that a given table/proc/etc. is safe to be dropped prior to dropping it. My current method is to do some checks, see how often stuff is being used, search code, then to rename the object ZZ_. I let the object exist for two weeks as ZZ_ before I finally script it out and drop it.

I decided to write a little script to search out the names of all my ZZ'd objects on a server (specifically User Tables, Procs and Agent Jobs) and store them in a temp table. Next I email the list to my development team offering them one last chance to save the object, and set about scripting it out. The script also creates a DROP script for you to run, so when you are ready, you can just run the script.

Works pretty well. Please comment below on how you facilitate removing old objects, is your method different from mine?

/*******************************
** Created By: Eric Zierdt **
** ericzierdt@gmail.com **
** ericemployed.blogspot.com **
*******************************/
IF object_id('tempdb..#ObjectList') IS NOT NULL
BEGIN
   DROP TABLE #ObjectList
END
CREATE TABLE #ObjectList
(
 DBName VARCHAR(100)
 ,ObjectName VARCHAR(100)
 ,ObjectType VARCHAR(2)
 ,ObjectType_Desc VARCHAR(100)
)

DECLARE @DBName AS VARCHAR(MAX)
  ,@SQL AS VARCHAR(MAX)
  
DECLARE ZCursor CURSOR FOR 
 SELECT name
 FROM   sys.databases
 WHERE  owner_sid <> '0x01'
 ORDER BY name
OPEN ZCursor
FETCH NEXT FROM ZCursor
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN 
 PRINT 'Starting ' + @DBName
 SET @SQL = 'USE [' + @DBName + ']
  SELECT ''' + @DBName + ''' AS DBName, name,type,type_desc FROM sys.objects WHERE name LIKE ''%%zz%%'' ORDER BY CASE WHEN type = ''U'' THEN 1 ELSE 2 END, name
'
 INSERT INTO #ObjectList
         ( DBName, ObjectName,ObjectType,ObjectType_Desc )
 EXEC (@SQL)
 FETCH NEXT FROM ZCursor 
INTO @DBName
END
CLOSE ZCursor
DEALLOCATE ZCursor

USE msdb
INSERT INTO #ObjectList
 ( DBName, ObjectName,ObjectType,ObjectType_Desc )
SELECT 'msdb',name,'A','Agent Job' 
FROM dbo.sysjobs 
WHERE name LIKE '%%zz%'


--  SELECT * FROM #ObjectList

-- RUN THE CODE BELOW TO PRINT OUT THE DROP COMMANDS FOR THE OBJECTS
/*
DECLARE @DBName AS VARCHAR(MAX)
  ,@SQL AS VARCHAR(MAX)
  ,@ObjectName AS VARCHAR(MAX)
  ,@ObjectType AS VARCHAR(2)
  ,@Job_ID VARCHAR(257)
DECLARE DeleteCursor CURSOR FOR 
 SELECT DBName, ObjectName,ObjectType FROM #ObjectList --WHERE ObjectType = 'U'
OPEN DeleteCursor
FETCH NEXT FROM DeleteCursor
INTO @DBName,@ObjectName,@ObjectType
WHILE @@FETCH_STATUS = 0
BEGIN 
 IF @ObjectType = 'U'
 BEGIN
  SET @SQL = 'DROP TABLE [' + @DBName + '].[dbo].[' + @ObjectName + ']
GO'
 END
 ELSE IF @ObjectType = 'P'
 BEGIN
  SET @SQL = 'USE [' + @DBName + ']
DROP PROCEDURE [dbo].[' + @ObjectName + ']
GO'
 END
 ELSE IF @ObjectType = 'A'
 BEGIN
  SELECT @Job_ID = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'' + @ObjectName + ''
  SET @SQL = 'EXEC msdb.dbo.sp_delete_job @job_id=N''' + @Job_ID + ''', @delete_unused_schedule=1
GO'
 END
 ELSE
  PRINT 'Unknown Object Type Found: [' + @ObjectType + ']'  
 PRINT @SQL
 FETCH NEXT FROM DeleteCursor 
INTO @DBName,@ObjectName,@ObjectType
END
CLOSE DeleteCursor
DEALLOCATE DeleteCursor
*/