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
*/
