A Script to Find SQL Server Stored Procedure Dependencies

$0You’ve searched through MSDN, Google Groups, and all along the yellow brick road of developer web sites looking for a way to generate a sql script for your stored procedures in order of dependency, but to no avail. Why? You, like myself, prefer not to see a bunch of error messages in Query Analyzer when we deploy our stored procedures to either the QA or Production environment. I don’t like having to scroll through all the messages looking for valid errors just in case I missed something else in the deployment. Well, you haven’t managed to reach the great and powerful Oz, but perhaps I can help just the same.$0$0Over the years, I’ve found that the dependencies for stored procedures aren’t always accurate. It relies on the sysdepends table, which doesn’t always have the proper relational keys. So, I opted to try and write a script that would look at the actual source code of each procedure in the database to see if it referenced one or more procedures. If so, flag it so that it could be generated first. A counter was implemented to keep track of the number of instances a given procedure is called by the other procedures. The higher the counter, the more likely it was necessary to have this procedure generated before others. Granted, it is not a perfect science, but my testing over several databases with dependent stored procedures has shown my methodology to handle most situations.$0$0There is one small flaw that doesn’t create problems, but could make your SQL script larger than it needs to be. If the source code for the stored procedure exceeds the maximum allowed bytes in the syscomments table, SQL Server spreads the source code across multiple records and uses sp_helptext to combine the code back into one long string again. The stored procedures that fall into this category are generated multiple times using my script. In order to keep this example clean and easy to read, I didn’t implement any checking to see if a procedure had already been processed before processing it. Leaving it as is simply drops and creates these procedures more times than is really necessary.$0$0The script below is meant to be run in SQL Server’s Query Analyzer with the option to show column headers turned off. Upon completion, you can just copy and paste the generated SQL wherever you need it. I happen to work over Terminal Services quite a bit and simply pasting the SQL in Query Analyzer on the QA or Production database server is pretty convenient. If you need the output in a file, just adjust the option in Query Analyzer. You’ll also need to change the @ProcUser variable value to the database user you wish to grant execute permissions for.$0$0As I mentioned, this isn’t an exact science, but is the best I could come up with or find using straight SQL Server code. If you can suggest a different method, please post it to our forums at 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


No comments yet... Be the first to leave a reply!