Getting a list of SQL Server modules to refresh

If you have a table and a view based on that table, and alter the table (add a column perhaps), then you will need to refresh the view. This can be done with the sp_refreshview or sp_refreshsqlmodule procedure.

If you have many views, procedures, and functions that refer to each other, then picking which ones to refresh in which order may be a daunting task. When I was put in this situation recently, I decided to write a utility to refresh everything. The biggest task was to produce a list of all the modules with the bottom-most modules first.

A recursive Common Table Expression seemed the way to go, so I wrote this:

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 so.name
, so.[type]
, c.[Rank]
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]
WHERE so.[type] IN ( 'V' , 'P' , 'FN' ) 
ORDER BY c.[Rank],so.Name,so.[type]

This produces the list of modules; you can iterate it and execute sp_refreshsqlmodule on each in turn.