SQL 删除视图（如果存在）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36133752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop view if exists
提问by 4est
I have script where I want to first drop view and then create it. I know how to drop table:
我有脚本，我想首先删除视图然后创建它。我知道如何删除表：
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'table1' AND type = 'U') DROP TABLE table1;
so I did the same for views:
所以我对视图做了同样的处理：
IF EXISTS (SELECT * FROM sys.views WHERE name = 'view1' AND type = 'U') DROP VIEW view1;
create view1 as(......)
and then I got error:
然后我得到了错误：
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' 必须是查询批处理中的第一条语句。
回答by TheGameiswar
your exists syntax is wrong and you should seperate DDL with go like below
你的存在语法是错误的，你应该像下面一样将 DDL 与 go 分开
if exists(select 1 from sys.views where name='tst' and type='v')
drop view tst;
go
create view tst
as
select * from test
you also can check existence test, with object_id like below
你也可以检查存在性测试，object_id如下
if object_id('tst','v') is not null
drop view tst;
go
create view tst
as
select * from test
In SQL 2016,you can use below syntax to drop
在 SQL 2016 中，您可以使用以下语法删除
Drop view if exists dbo.tst
From SQL2016 CU1,you can do below
从 SQL2016 CU1，你可以在下面做
create or alter view vwTest
as
select 1 as col;
go
回答by Manngo
Regarding the error
关于错误
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
Microsoft SQL Server has a quirky reqirement that CREATE VIEWbe the onlystatement in a batch. This is also true of a few other statements, such as CREATE FUNCTION. It is nottrue of CREATE TABLE, so go figure …
Microsoft SQL Server 有一个奇怪的要求，即批处理中CREATE VIEW的唯一语句。其他一些语句也是如此，例如CREATE FUNCTION. 它是不是真正的CREATE TABLE，所以大家...
The solution is to send your script to the server in small batches. One way to do this is to select a single statement and execute it. This is clearly inconvenient.
解决方案是将您的脚本小批量发送到服务器。一种方法是选择一个语句并执行它。这显然很不方便。
The more convenient solution is to get the client to send the script in small isolated batches.
更方便的解决方案是让客户端以小批量的方式发送脚本。
The GOkeyword is not strictly an SQL command, which is why you can't end it with a semicolon like real SQL commands. Instead it is an instruction to the client to break the script at this point and to send the portion as a batch.
该GO关键字不是严格意义上的SQL命令，这就是为什么你不能像真正的SQL命令的分号结束。相反，它是对客户端的指令，在此时中断脚本并将该部分作为批处理发送。
As a result, you end up writing something like:
结果，你最终会写出类似的东西：
DROP VIEW IF EXISTS … ;
GO
CREATE VIEW … AS … ;
GO
None of the other database servers I have encountered (PostgreSQL, MySQL, Oracle, SQLite) have this quirk, so the requirement appears to be Microsoft Only.
我遇到的其他数据库服务器（PostgreSQL、MySQL、Oracle、SQLite）都没有这个怪癖，所以要求似乎是 Microsoft Only。
回答by Anwar Ul Haq
DROP VIEW if exists {ViewName}
Go
CREATE View {ViewName} AS
SELECT * from {TableName}
Go
回答by user230910
To cater for the schema as well, use this format in SQL 2014
为了满足架构，请在 SQL 2014 中使用此格式
if exists(select 1 from sys.views V inner join sys.[schemas] S on v.schema_id = s.schema_id where s.name='dbo' and v.name = 'someviewname' and v.type = 'v')
drop view [dbo].[someviewname];
go
And just throwing it out there, to do stored procedures, because I needed that too:
只是把它扔在那里，做存储过程，因为我也需要：
if exists(select 1
from sys.procedures p
inner join sys.[schemas] S on p.schema_id = s.schema_id
where
s.name='dbo' and p.name = 'someprocname'
and p.type in ('p', 'pc')
drop procedure [dbo].[someprocname];
go
