首页 > 数据库技术 > 详细

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

时间:2020-07-24 14:34:50      阅读:81      评论:0      收藏:0      [点我收藏+]
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;

 

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

原文:https://www.cnblogs.com/zldqpm/p/13371273.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!