刷新数据库自己定义的视图

DECLARE @TotalCount INT           = 0,
        @NowCount   INT           = 0,
        @v_Name     NVARCHAR(300) = N‘‘;
--创建临时表
CREATE TABLE #Refresh_View 
( ID INT IDENTITY(1, 1),
  v_Name NVARCHAR(300),
  PRIMARY KEY (ID));
INSERT INTO #Refresh_View
SELECT name AS v_Name FROM sys.all_objects WHERE [type]= v AND schema_id =1;
SELECT @TotalCount = COUNT(*) FROM #Refresh_View;
SET @NowCount = 1;
--循环所有视图并刷新视图
WHILE (@NowCount <= @TotalCount)
BEGIN
    SELECT @v_Name = v_Name
      FROM #Refresh_View
     WHERE ID = @NowCount;
    BEGIN TRY
        EXEC sp_refreshview @v_Name;
        PRINT 视图 + @v_Name + 成功;
    END TRY
    BEGIN CATCH
        PRINT 视图 + @v_Name + 失败;
    END CATCH;
    SET @NowCount=@NowCount+1;
END;
--删除零时表
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id   = OBJECT_ID(Ntempdb..#Refresh_View)AND type = U)
BEGIN
    DROP TABLE #Refresh_View;
END;

 

刷新数据库自己定义的视图

上一篇:mongoDB复制集修改 oplog 大小


下一篇:SQL之DDL、DML、DCL、TCL