Dropping a View
Learn how to permanently remove an existing SQL Server View using the DROP VIEW statement.
What is DROP VIEW?
The DROP VIEW statement is used to permanently remove an existing View from a SQL Server database.
When a View is dropped, the View object and its definition are removed from the database.
Dropping a View does not delete the underlying table data. The tables used by the View remain in the database unless they are separately deleted.
DROP VIEW Syntax
DROP VIEW ViewName;
Syntax Explanation
- DROP VIEW specifies that a View should be removed.
- ViewName is the name of the View that needs to be deleted.
Example View
Suppose we have a View named EmployeeDetailsView:
CREATE VIEW EmployeeDetailsView
AS
SELECT
EmployeeId,
EmployeeName,
Department,
Salary
FROM Employees;
We can retrieve its data using:
SELECT *
FROM EmployeeDetailsView;
Dropping the View
To permanently remove the View, use:
DROP VIEW EmployeeDetailsView;
After executing this statement, the EmployeeDetailsView View is removed from the database.
Verify That the View Was Dropped
We can check the Views available in the current database using the system catalog:
SELECT
name
FROM sys.views;
If the View was successfully dropped, its name will no longer appear in the list.
Dropping Multiple Views
Multiple Views can be removed by executing separate DROP VIEW statements.
DROP VIEW EmployeeDetailsView;
DROP VIEW EmployeeSalaryView;
Each statement removes its corresponding View.
Using DROP VIEW IF EXISTS
SQL Server also supports checking whether a View exists before dropping it.
DROP VIEW IF EXISTS EmployeeDetailsView;
This statement drops the View if it exists. If the View does not exist, SQL Server does not raise the usual missing-object error for the DROP operation.
DROP VIEW vs DELETE
| DROP VIEW | DELETE |
|---|---|
| Removes a View object. | Removes rows from a table. |
| Removes the View definition. | Does not remove the table itself. |
| Does not directly delete underlying table data. | Deletes selected table records. |
Important Warning
Dropping a View removes the View object from the database. Applications, queries, reports, or stored procedures that depend on that View may stop working.
Always verify dependencies before dropping a View in a production database.
Best Practices
- Verify the View name before executing DROP VIEW.
- Check whether applications depend on the View.
- Use DROP VIEW IF EXISTS when appropriate.
- Avoid dropping Views directly in production without checking dependencies.
- Test database changes in a development environment before applying them to production.
Summary
The DROP VIEW statement permanently removes an existing SQL Server View. Dropping a View removes the View object and its definition, but it does not directly delete the data stored in the underlying tables. Always check dependencies before dropping a View.