How To Drop View in SQL Server

SQL views allow you to store result set of queries as a virtual table that you can reference often, without rewriting its underlying SQL query. However, sometime you have to drop view in SQL Server because you don’t need it anymore. In this article, we will look at how to drop view in SQL server.

How To Drop View in SQL Server

It is very easy to drop view in SQL server using DROP VIEW command. Here is its syntax

DROP VIEW [IF EXISTS] view_name;
or
DROP VIEW [IF EXISTS] schema_name.view_name;

In the above statement you need to specify the view name you want to delete. If you don’t specify schema name, SQL Server will try to delete view from active schema. If the view does not exist, then you will get an error message.

To avoid this error, you can optionally add IF EXISTS keyword. In this case, SQL server will delete view only if it exists.

Also read : How to Drop Index in SQL Server

Let us say you have the following view order_view

create view order_view
as
select * from orders;

Here is the SQL query to remove this view.

drop view if exists order_view;

Also read : How to Create Index in SQL Server

You can also drop multiple views at once. Here is the syntax to delete multiple views in SQL Server.

DROP VIEW [IF EXISTS]
view_name1,
view_name2,
…;

OR

DROP VIEW [IF EXISTS]
schema_name.view_name1,
schema_name.view_name2,
…;

In the above statement, you need to specify all views that you want to delete, in a comma-separated list. If you don’t specify schema name, SQL Server will try to delete view from active schema.

Also read : Top 5 Data Modeling Tools for SQL Server

Here is an example to remove multiple views in SQL Server

drop view
products.product_view,
orders.order_view;

As you can see, it is very easy to remove view in SQL server.