[sql] 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.

This question is related to sql sql-server view create-view

The answer is


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

DROP VIEW if exists {ViewName}
Go
CREATE View {ViewName} AS 
SELECT * from {TableName}  
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.


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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to view

Empty brackets '[]' appearing when using .where SwiftUI - How do I change the background color of a View? Drop view if exists Difference between View and ViewGroup in Android How to make a view with rounded corners? How to remove all subviews of a view in Swift? How to get a view table query (code) in SQL Server 2008 Management Studio how to add button click event in android studio How to make CREATE OR REPLACE VIEW work in SQL Server? Android findViewById() in Custom View

Examples related to create-view

Drop view if exists