-- Stored Procedure: dbo.GetDropObsoleteProcsForDB -- Bill Wunder use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDropObsoleteProcsForDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetDropObsoleteProcsForDB] GO CREATE PROCEDURE [dbo].[GetDropObsoleteProcsForDB] @DbName varchar(128) AS /******************************************************************************************************* * dbo.GetDropObsoleteProcsForDB.PRC * Creator: Bill W * Date: 3-5-2004 * * Description: Build a disposition list for unused proc tracking for a database * * Usage: print char(47) + '**************************************************************' print ' Obsolete Procedure Clean up script' print ' Server: ' + @@servername print ' generated on ' + cast(getdate() as varchar(20)) print '****************************************************************'+char(47) exec sp_msforeachdb 'use ? EXECUTE admin.dbo.GetDropObsoleteProcsForDB ''?''' * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- declare @SQLStr nvarchar(4000) , @line varchar(255) , @ObjectId int , @rowid int , @maxrowid int --------------------------------------------- -- create temp tables --------------------------------------------- create table #lines(data varchar(255),rowid int identity(1,1) primary key) --------------------------------------------- -- set session variables --------------------------------------------- SET NOCOUNT ON --------------------------------------------- -- body of stored procedure --------------------------------------------- set @SQLStr = 'select @ObjectId = object_id(''' + @DbName + '.dbo.UnusedProcExecutionLog'',''U'')' exec sp_executeSQL @SQLStr, N'@ObjectId int OUTPUT', @ObjectId OUTPUT if @ObjectId is not null begin print '/**************************************************************' print ' Database: ' + @DbName print '****************************************************************/' print '' set @SQLStr = 'use ' + @DbName + ' select case when u.ProcName is null then case when c.id is null then ''-- not tracked, check for invalid dependancies'' else '''' end + ''use ['' + db_name() + ''] drop procedure ['' + user_name(o.uid) + ''].['' + o.name + '']'' else ''-- remove UnusedProcExecutionLog insert from procedure ['' + db_name() + ''['' + user_name(o.uid) + ''].['' + o.name + '']'' end from dbo.sysobjects o left join dbo.syscomments c on o.id = c.id left join admin.dbo.ProceduresFoundInCache p on o.name = p.ProcedureName left join dbo.UnusedProcExecutionLog u on o.name = u.ProcName where o.type = ''P'' and p.ProcedureName is null and o.name not like ''dt_%'' and c.text like ''%UnusedProcExecutionLog%''' insert #lines (data) exec sp_executeSQL @SQLStr select @maxrowid = @@identity set @rowid = 1 while @rowid <= @maxrowid and @maxrowid is not null begin select @line = data from #lines where rowid = @rowid print @line select @rowid = min(rowid) from #lines where rowid > @rowid end print '' -- print'drop table [' + @DbName + '].[dbo].[UnusedProcExecutionLog]' end RETURN GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO