且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何在SQL Server中更改视图

更新时间:2022-10-18 09:28:07

   -   我希望您的视图名称相同并存在于所有数据库中。如果是,则查询下方将对您有帮助。 
DECLARE @sqlcommand NVARCHAR (MAX), @ db_py NVARCHAR ( 50
SET @ db_py = ' PY202'
SET @sqlcommand = ' USE' + QUOTENAME( @db_py )+ '
GO
ALTER VIEW test AS
SELECT * FROM PY_S01
UNI ON ALL
SELECT * FROM PY_S02'
;

- 您可以在alter view命令中直接使用DB name,如下面的查询。 / span>

SET @sqlcommand = ' ALTER VIEW测试AS SELECT * FROM PY_S01 UNION ALL SELECT * FROM PY_S02';
SET @sqlcommand = REPLACE( @ sqlcommand ' ALTER VIEW test'' ALTER VIEW' + SPACE( 1 )+ QUOTENAME( @ db_py )+ ' 。DBO.test');

EXEC @ sqlcommand


How to modify the view when the database name and SQL command are variables
An example as below:
declare @sqlcommand nvarchar(max),@db_py nvarchar(50)
set @db_py='PY202'
set @sqlcommand='alter view test as
select * from PY_S01
union all
select * from PY_S02'
exec(N'use '''+@db_py+''';exec sp_executesql N'''+@sqlcommand+'''')

What I have tried:

1.I change the last commmand to
exec(N'use PY202;exec sp_executesql N'''+@sqlcommand+'''')
execute successfull!
2.set @sqlcommand='use '+@db_py+char(10)+'GO'+char(10)+'alter view test as
select * from PY_S01
union all
select * from PY_S02'
exec(@sqlcommand)

execute fail
but i print @sqlcommand and copy the result to execute ,success

--i hope your view name is same and existed in all db .If yes, below query will help you.
DECLARE @sqlcommand NVARCHAR(MAX),@db_py NVARCHAR(50)
SET @db_py='PY202'
SET @sqlcommand='USE '+QUOTENAME(@db_py)+' 
                  GO
				 ALTER VIEW test AS
                    SELECT * FROM PY_S01
                     UNION ALL
                    SELECT * FROM PY_S02';

--you can directly use DB name in alter view command as like below query.

SET @sqlcommand='ALTER VIEW test AS SELECT * FROM PY_S01  UNION ALL SELECT * FROM PY_S02';
SET @sqlcommand=REPLACE(@sqlcommand,'ALTER VIEW test','ALTER VIEW'+SPACE(1)+QUOTENAME(@db_py)+'.DBO.test ');

EXEC(@sqlcommand)