SQL Server DROP VIEW

Summary: in this tutorial, you will learn how to use the SQL Server DROP VIEW statement to remove an existing view.

To remove a view from a database, you use the DROP VIEW statement as follows:

DROP VIEW [IF EXISTS] schema_name.view_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the view that you want to drop after the DROP VIEW keywords. If the view belongs to a schema, you must also explicitly specify the name of the schema to which the view belongs.

If you attempt to remove a view that does not exist, SQL Server will issue an error. The IF EXISTS clause prevents an error from occurring when you delete a view that does not exist.

To remove multiple views, you use the following syntax:

DROP VIEW [IF EXISTS] 
    schema_name.view_name1, 
    schema_name.view_name2,
    ...;
Code language: SQL (Structured Query Language) (sql)

In this syntax, the views are separated by commas.

Note that when you drop a view, SQL Server removes all permissions for the view.

SQL Server DROP VIEW examples

We will use the sales.daily_sales and sales.staff_sales views created in the CREATE VIEW tutorial for the demonstration.

Removing one view example

The following example shows how to drop the sales.daily_sales view from the sample database:

DROP VIEW IF EXISTS sales.daily_sales;
Code language: SQL (Structured Query Language) (sql)

Removing multiple views example

The following statement creates a view named product_catalogs for demonstration purpose:

CREATE VIEW sales.product_catalog
AS
SELECT 
    product_name, 
    category_name, 
	brand_name,
    list_price
FROM 
    production.products p
INNER JOIN production.categories c 
    ON c.category_id = p.category_id
INNER JOIN production.brands b
	ON b.brand_id = p.brand_id;
Code language: SQL (Structured Query Language) (sql)

The following statement removes both sales.staff_sales and sales.product_catalog views at the same time:

DROP VIEW IF EXISTS 
    sales.staff_sales, 
    sales.product_catalogs;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server DROP VIEW statement to remove one or more views from the database.

Was this tutorial helpful?