Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Getting a List of SQL Server Modules to Refresh

0.00/5 (No votes)
26 Apr 2012CPOL 12.5K  
How to get a list of SQL Server modules to refresh

Introduction

Here it is wrapped in a procedure:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)