Getting a List of SQL Server Modules to Refresh

CREATE PROCEDURE [dbo].[RefreshAllModules]
AS
BEGIN
  DECLARE @Name NVARCHAR(128)
  DECLARE @Stmt NVARCHAR(128)
 
  DECLARE ModuleList CURSOR FOR
    WITH cte AS
    (
      SELECT DISTINCT lft.referenced_id [object_id]
      , 0 [Rank]
      FROM sys.sql_expression_dependencies lft
      LEFT OUTER JOIN sys.sql_expression_dependencies rgt
      ON lft.referenced_id=rgt.referencing_id
      WHERE rgt.referencing_id IS NULL
    UNION ALL
      SELECT lft.referencing_id [object_id]
      , [Rank] + 1
      FROM sys.sql_expression_dependencies lft
      INNER JOIN cte rgt
      ON lft.referenced_id=rgt.[object_id]
    )
     
    SELECT '''' + ss.name +'.' + so.name + '''' 'name'
    FROM
    (
      SELECT [object_id]
      , MAX([Rank]) [Rank]
      FROM cte 
      GROUP BY [object_id]
    ) c
    INNER JOIN sys.objects so 
    ON c.[object_id]=so.[object_id]
    INNER JOIN sys.Schemas ss
    ON so.schema_id=ss.schema_id
    WHERE so.[type] IN ( 'V' , 'P' , 'FN' ) 
    ORDER BY c.[Rank],so.Name,so.[type]
 
  OPEN ModuleList
 
  FETCH NEXT FROM ModuleList INTO @Name
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @Stmt = 'EXECUTE sp_refreshsqlmodule ' + @Name
 
    PRINT @Stmt
    
    EXECUTE (@Stmt)
    
    FETCH NEXT FROM ModuleList INTO @Name
  END
  CLOSE ModuleList
  DEALLOCATE ModuleList
 
END

BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian

---------------

"I would be looking for better tekkies, too. Yours are broken." -- Paul Pedant

"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Use vertical and horizontal whitespace generously. Generally, all binary operators except '.' and '->' should be separated from their operands by blanks."

"Omit needless local variables." -- Strunk... had he taught programming