4est 4est - 11 days ago 6
SQL Question

Drop view if exists

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.

Answer

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 with 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

Drop view dbo.tst if exists

From SQL2016 CU1,you can do below

create or alter view vwTest
as
 select 1 as col;
go