sp_refreshview

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

CREATE PROCEDURE [dbo].[uspRefreshViews]
AS
BEGIN;
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	
BEGIN
   SET NOCOUNT ON  
   DECLARE @views TABLE
   (   id INT IDENTITY(1,1) not null,
      schemaName NVARCHAR(128),
      viewName NVARCHAR(128) not null
   );
   DECLARE 
      @maxID INT,
      @id INT = 0,
      @aViewName NVARCHAR (128) = '',
      @aSchemaViewName NVARCHAR(258)

   INSERT INTO  @views          
      SELECT sch.name, vw.name
         FROM sys.views   vw
            INNER JOIN sys.schemas sch ON sch.schema_id = vw.schema_id 
            
   SELECT @maxID = coalesce(@@ROWCOUNT,0)

   WHILE @id <> @maxID
   BEGIN
      SELECT TOP  1 @id = id, @aSchemaViewName = schemaName + '.' + viewName
         FROM @views
         WHERE @id < id      
         ORDER BY  id ASC
         
      PRINT 'Refreshing ' + @aSchemaViewName
      EXECUTE sp_refreshview @aSchemaViewName
   END
END

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql?view=sql-server-ver15

Last modified: May 29, 2020

Author

Comments

Write a Reply or Comment