替换所有视图中某些字段

 

DECLARE @TotalCount INT = 0,
@NowCount INT = 0,
@v_Name NVARCHAR(300) = N'',
@v_Des NVARCHAR(max) = N'';
--创建临时表
CREATE TABLE #Refresh_View 
( ID INT IDENTITY(1, 1),
v_Name NVARCHAR(300),
v_Des NVARCHAR(max),
PRIMARY KEY (ID));
INSERT INTO #Refresh_View

SELECT 
OBJECT_NAME(sm.object_id) AS v_Name ,
sm.definition AS v_Des
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type='V'

SELECT @TotalCount = COUNT(*) FROM #Refresh_View;
SET @NowCount = 1;
--循环所有视图并替换视图中某些字段
WHILE (@NowCount <= @TotalCount)
BEGIN
BEGIN TRY
SELECT @v_Name = v_Name,
@v_Des = v_Des
FROM #Refresh_View
WHERE ID = @NowCount;
--EXEC sp_refreshview @v_Name;
UPDATE #Refresh_View SET v_Des = replace (v_Des,'被修改字段','修改为的字段') where id=@NowCount
SELECT @v_Name = v_Name,
@v_Des = v_Des
FROM #Refresh_View
WHERE ID = @NowCount;
PRINT '视图' + @v_Name + '替换成功';
exec('drop view '+@v_Name)
exec(@v_Des)
END TRY
BEGIN CATCH
PRINT '视图' + @v_Name + '替换失败';
select @v_Name as [view], ERROR_MESSAGE() as msg
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;