用SQL脚本移除视图中存在的机器名
用SQL脚本移除视图中存在的机器名
例子: msccdr.cdr.DimRMAReturnMethod
CREATE VIEW CDR.DimRMAReturnMethod ( ReturnMethodId,srcReturnMethodCode,ReturnMethodName,IsDeleted ,TrackingID,ETLInsertDtTm ,ETLUpdateDtTm)
AS SELECT t.ReturnMethodId,t.srcReturnMethodCode,t.ReturnMethodName,t.IsDeleted,t.TrackingID,t.ETLInsertDtTm,t.ETLUpdateDtTm
FROM [MSCBISQLX01].[CDR].dbo.DimRMAReturnMethod t WITH (NOLOCK)
处理方法
*.查询出视图中存在机器名的视图信息
USE Database
GO
SELECT
b.name,
c.referenced_server_name,
c.referenced_database_name,
c.referenced_schema_name,
a.definition,
b.create_date,
b.modify_date
FROM sys.sql_modules AS a
JOIN
sys.objects AS b
ON a.object_id = b.object_id
JOIN
sys.sql_expression_dependencies AS c
ON b.object_id = c.referencing_id
WHERE b.type = 'V'
AND c.referenced_server_name IS NOT NULL
*.修改视图中存在机器名的视图
SET NOCOUNT ON;
DECLARE @definition VARCHAR(MAX)
DECLARE @ServerN VARCHAR(50)
DECLARE View_Cursor CURSOR SCROLL
FOR (
SELECT
c.referenced_server_name,
a.definition
FROM sys.sql_modules AS a
JOIN
sys.objects AS b
ON a.object_id = b.object_id
JOIN
sys.sql_expression_dependencies AS c
ON b.object_id = c.referencing_id
WHERE b.type = 'V'
AND c.referenced_server_name IS NOT NULL
)
OPEN View_Cursor;
FETCH NEXT FROM View_Cursor INTO @ServerN,@definition;
WHILE @@fetch_status = 0
BEGIN
SELECT @definition = REPLACE (@definition,'CREATE VIEW','ALTER VIEW')
SELECT @definition = REPLACE (@definition,'['+@ServerN+']'+'.','')
--print(@definition);
exec(@definition);
FETCH NEXT FROM View_Cursor INTO @ServerN,@definition;
END
CLOSE View_Cursor;
DEALLOCATE View_Cursor;
GO
以上处理方法仅供参考，如有处理不完善的地方；请大家指出共同学习。
