Getting a List of SQL Server Modules to Refresh

Introduction

Here it is wrapped in a procedure:

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

This version also includes the name of the schema.