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

User · Answer

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

User · Answer

DROP VIEW if exists  ViewName  Go CREATE View  ViewName  AS  SELECT   from  TableName    Go

User · 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 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

User · Answer

To cater for the schema as well  use this format in 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

[sql] Drop view if exists

Examples related to sql

Examples related to sql-server

Examples related to view

Examples related to create-view