刷新数据库自己定义的视图
时间：2021-07-01 10:21:17
帮助过：5人阅读
@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(N
‘tempdb..#Refresh_View‘)
AND type
= ‘U‘)
BEGIN
DROP TABLE #Refresh_View;
END;
刷新数据库自己定义的视图
标签：div 定义 key sel cat HERE sele 数据 type
