SQL Server Stored Procedure Script Generator By Dependency

By Robbe D. Morris

Printer Friendly Version


Robbe & Melisa Morris
You've searched through MSDN, Google Groups, and all along the yellow brick road of developer web sites looking for a way to generate sql script for your stored procedures in order of dependency 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 for Article Discussions because I'd love to hear from you.  Feel free to take the sample and adjust it as needed for both commerical or personal use.  Please take a moment to rate this article (opens in new window).


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

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.