有没有一种方法可以使用TSQL脚本替换代码并更新视图

Question

我在数据库中有大约20个视图，该数据库引用另一个数据库，我们称其为数据库A。我需要一种用脚本更新这些视图的方法，以指向其他数据库，数据库B。是否有一种方法可以使用单个TSQL脚本在视图中使用数据库B的名称替换数据库A的名称并保持视图完整？我可以进行替换并将视图输出到查询窗口，但我想执行生成的变更语句，而不必手动运行输出。

Update 我想做的类似于以下操作：https：//stackoverflow.com/a/2983927/6084613，但也由脚本执行的输出。这可能吗？

Answer 1

我在SQL下方创建了同样的事情。此脚本将获取2个输入1.旧数据库名称2.新的DB名称3.要修改的视图列表

declare @OldDb  varchar(250),  @newDB  varchar(250)
select @OldDb = '' , ---------->>> provide old db name
@newDB = '' ---------->>> provide new  db name
    create table #ViewList (Id int identity , ViewName varchar(250))
insert into #ViewList 
select TABLE_NAME from INFORMATION_SCHEMA.VIEWS  where TABLE_NAME IN ( ) ---------->>> provide you view list 
create table #ViewDef(  ViewDef nvarchar(max) , ViewName varchar(250) ,Id int   )
declare @minId int , @maxid int , @sql nvarchar(max) ='' , @ViewName varchar(250)
select @minId = min(Id) , @maxid = max(id) from #ViewList
while @minId <= @maxid
begin 
select @ViewName = ViewName from #ViewList where id = @minId
set @sql = '    insert into #ViewDef (ViewDef)
exec sp_helptext '+ @ViewName +' 
update #ViewDef
set ViewName  = '''+ @ViewName +''', 
id = ' + cast(@minId as varchar(10)) +'
where id is null
update #ViewDef 
set ViewDef = replace(ViewDef , '''+ @OldDb+''','''+ @newDB +''')  
where id = ' + cast(@minId as varchar(10)) +'
update #ViewDef 
set ViewDef = replace(ViewDef , ''create'',''alter'')  
where id = ' + cast(@minId as varchar(10)) +'
'
exec sp_executesql  @sql 
SET @sql = ''
select @sql = @sql + ViewDef from #ViewDef  where id =   @minId   
exec sp_executesql  @sql
--print @sql
set @minId = @minId +1 
end 

**请测试脚本并节省您的旧定义，以避免上述任何错误

Answer 2

您只需要将查询定义放入变量中，然后使用sp_executesql。

执行

但是：使用引用脚本而没有更多工作，我会谨慎。例如，并非所有视图都具有确切的文本"创建视图"。如果手写，有些可能是"创建视图"或"创建视图"。另外，" db1"可能我写了'[db1]'。

因此，要么添加更复杂的逻辑，要么在退出之前通过眼睛验证了所有内容。

   Declare @queryDef nvarchar(max)
   SELECT @queryDef = REPLACE (REPLACE (sm.definition, 'CREATE VIEW', 'ALTER VIEW'), 'DB1.', 'DB2.') 
   FROM sys.sql_modules sm JOIN sys.objects o 
        ON sm.object_id = o.object_id
    WHERE
        sm.definition LIKE '%DB1.%' AND o.type = 'V'
    print @queryDef
    exec sp_executeSql @querydef

Answer 3

使用SimonB Idea，我创建了一个自动进行的循环：

DECLARE @queryDef NVARCHAR(max)
WHILE EXISTS (
                    SELECT 1
                    FROM sys.sql_modules    sm 
                    JOIN    sys.objects  o ON sm.object_id = o.object_id 
                    WHERE   sm.definition LIKE '%TEXT_TO_REPLACE%' 
                    AND o.type = 'V'
            )
BEGIN
    
                                                                    -- TO ALTER THE VIEW AUTOMATICALLY                                          
    SET @queryDef = (   SELECT TOP 1 Replace (Replace (sm.definition, 'CREATE VIEW', 'ALTER VIEW'), 
                                            'TEXT_TO_REPLACE', 
                                            'NEW_TEXT') 
                        FROM sys.sql_modules    sm 
                        JOIN    sys.objects  o ON sm.object_id = o.object_id
                        WHERE   sm.definition LIKE '%TEXT_TO_REPLACE%'
                        AND     o.type = 'V')
    
    EXEC sp_executeSql @queryDef
END