sqlsql-serverviewcreate-view

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.

Solution

  • 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