A Script to Find SQL Server Stored Procedure Dependencies

EggHeadCafe.com for Article Discussions because I’d love to hear from you. Feel free to take the sample and adjust it as needed for both commercial or personal use.$0$0$0$0Source Code Script Generation$0$0set nocount on$0$0declare @ProcName nvarchar(100) $0declare @ProcSortOrder int$0declare @MyCursor CURSOR $0declare @ProcUser varchar(100)$0$0select @ProcUser = ‘my user’$0$0declare @StoredProcs TABLE$0($0SortOrder int,$0ProcedureName varchar(100),$0ProcedureCode varchar(7500)$0)$0$0Insert Into @StoredProcs$0select 0,upper(SysObjects.Name),SysComments.Text$0from SysObjects,SysComments $0where SysObjects.type=’P’$0and (SysObjects.Category = 0)$0and (SysObjects.ID = SysComments.ID) $0order by SysObjects.Name ASC$0$0set nocount off$0$0SET @MyCursor = CURSOR FAST_FORWARD $0FOR $0select ProcedureName,$0SortOrder = (select count(*)$0from @StoredProcs B$0WHERE (A.ProcedureName <> B.ProcedureName)$0and (REPLACE(UPPER(B.ProcedureCode),B.ProcedureName,”)$0LIKE ‘%’ + upper(A.ProcedureName) + ‘%’)$0)$0from @StoredProcs A $0order by SortOrder Desc$0$0OPEN @MyCursor $0FETCH NEXT FROM @MyCursor $0INTO @ProcName,@ProcSortOrder $0$0WHILE @@FETCH_STATUS = 0 $0BEGIN $0$0PRINT ‘if exists (select * from dbo.sysobjects ‘$0PRINT ‘ where id = object_id(N’ + char(39) + ‘[dbo].[‘ + @ProcName + ‘]’ + char(39) + ‘)’$0PRINT ‘ and OBJECTPROPERTY(id, N’ + char(39) + ‘IsProcedure’ + char(39) + ‘) = 1) ‘$0PRINT ‘ drop procedure ‘ + @ProcName $0PRINT ‘ GO ‘$0PRINT ‘ SET QUOTED_IDENTIFIER OFF ‘$0PRINT ‘ GO ‘$0PRINT ‘ SET ANSI_NULLS OFF ‘ $0PRINT ‘ GO’$0exec sp_helptext @ProcName$0PRINT ‘ GO ‘$0PRINT ‘ SET QUOTED_IDENTIFIER OFF ‘$0PRINT ‘ GO ‘$0PRINT ‘ SET ANSI_NULLS ON ‘ $0PRINT ‘ GO ‘$0PRINT ‘ GRANT EXECUTE ON [dbo].[‘ + @ProcName + ‘] TO [‘ + @ProcUser + ‘]’$0PRINT ‘ GO ‘$0$0/* PRINT @ProcName + ‘ ‘ + cast(@ProcSortOrder as varchar(20)) */$0FETCH NEXT FROM @MyCursor $0INTO @ProcName,@ProcSortOrder $0END $0$0CLOSE @MyCursor $0DEALLOCATE @MyCursor$0$0$0$0About the Author$0$0Robbe Morris is a Senior Software Engineer (Decision Tools, TCO Schools, and TVO) at Gartner in Maitland, FL. He is a co-founder of EggHeadCafe.com which is hosted by his web site development and hosting company RobbeMorris.com Inc. $0]]>

Leave a comment

Your email address will not be published.