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.