如何在SQL Server中更改视图 [英] How alter view in SQL server

问题描述

当数据库名称和SQL命令是变量时如何修改视图

如下例子：

声明@sqlcommand nvarchar（ max），@ db_py nvarchar（50）

set @ db_py ='PY202'

set @sqlcommand ='alter view test as

select *来自PY_S01

联盟所有

选择*来自PY_S02'

exec（N'use'''+ @ db_py +'''; exec sp_executesql N'''+ @sqlcommand +''''）



我尝试过：



1.我将最后一个命令更改为

exec（N'use PY202; exec sp_executesql N'''+ @ sqlcommand +''''）

执行成功！

2.set @sqlcommand ='use'+ @ db_py + char（10）+'GO'+ char（10）+'alter view test as

从PY_S01中选择*

union all

select * from PY_S02'

exec（@sqlcommand）



执行失败

但我打印@sqlcommand并将结果复制到执行，success

解决方案

   -   我希望您的视图名称相同并存在于所有数据库中。如果是，则查询下方将对您有帮助。 
  DECLARE   @sqlcommand   NVARCHAR （MAX）， @ db_py  < span class =code-keyword> 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)

这篇关于如何在SQL Server中更改视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！