sql - Drop view if exists

Question

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.

question from:https://stackoverflow.com/questions/36133752/drop-view-if-exists

Answer 1

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