A Script to Find SQL Server Stored Procedure Dependencies

By : Randy Dyess Sep 26, 2003

Printer friendly

You'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.

Over 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.

There 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.

The 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.

As 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.

Source Code Script Generation

set nocount on

declare @ProcName nvarchar(100)
declare @ProcSortOrder int
declare @MyCursor CURSOR
declare @ProcUser varchar(100)

select @ProcUser = 'my user'

declare @StoredProcs TABLE
(
SortOrder int,
ProcedureName varchar(100),
ProcedureCode varchar(7500)
)

Insert Into @StoredProcs
select 0,upper(SysObjects.Name),SysComments.Text
from SysObjects,SysComments
where SysObjects.type='P'
and (SysObjects.Category = 0)
and (SysObjects.ID = SysComments.ID)
order by SysObjects.Name ASC

set nocount off

SET @MyCursor = CURSOR FAST_FORWARD
FOR
select ProcedureName,
SortOrder = (select count(*)
from @StoredProcs B
WHERE (A.ProcedureName <> B.ProcedureName)
and (REPLACE(UPPER(B.ProcedureCode),B.ProcedureName,'')
LIKE '%' + upper(A.ProcedureName) + '%')
)
from @StoredProcs A
order by SortOrder Desc

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ProcName,@ProcSortOrder

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'if exists (select * from dbo.sysobjects '
PRINT ' where id = object_id(N' + char(39) + '[dbo].[' + @ProcName + ']' + char(39) + ')'
PRINT ' and OBJECTPROPERTY(id, N' + char(39) + 'IsProcedure' + char(39) + ') = 1) '
PRINT ' drop procedure ' + @ProcName
PRINT ' GO '
PRINT ' SET QUOTED_IDENTIFIER OFF '
PRINT ' GO '
PRINT ' SET ANSI_NULLS OFF '
PRINT ' GO'
exec sp_helptext @ProcName
PRINT ' GO '
PRINT ' SET QUOTED_IDENTIFIER OFF '
PRINT ' GO '
PRINT ' SET ANSI_NULLS ON '
PRINT ' GO '
PRINT ' GRANT EXECUTE ON [dbo].[' + @ProcName + '] TO [' + @ProcUser + ']'
PRINT ' GO '

/* PRINT @ProcName + ' ' + cast(@ProcSortOrder as varchar(20)) */
FETCH NEXT FROM @MyCursor
INTO @ProcName,@ProcSortOrder
END

CLOSE @MyCursor
DEALLOCATE @MyCursor

About the Author

Robbe 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.

Your Name
Email	(Emails will not be displayed on the site or used for promotional purposes)
Comment
Type characters in the image	(case sensitive)