Drop view if exists
your exists syntax is wrong and you should seperate DDL with go like below
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
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
Drop view if exists dbo.tst
From SQL2016 CU1,you can do below
create or alter view vwTest
as
select 1 as col;
go
Regarding the error
'CREATE VIEW' must be the first statement in a query batch.
Microsoft SQL Server has a quirky reqirement that CREATE VIEW
be the only statement in a batch. This is also true of a few other statements, such as CREATE FUNCTION
. It is not true of CREATE TABLE
, so go figure …
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 GO
keyword 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.
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.