Using the DROP VIEW Statement to Remove a View

To remove a database view that you no longer need, execute the DROP VIEW statement. Unlike the DROP TABLE statement, the DROP VIEW command does not erase any database tables or data. When you DROP a view, the DBMS simply removes its definition (the name and the SELECT statement that defines the view) from the system tables. If you later decide you need the view again, simply use the CREATE VIEW statement or a tool like the MS-SQL Server Create View Wizard to re-create the view. As long as the underlying table is still in the database, re-creating the view will bring back the virtual table and its data.

The syntax of the DROP VIEW statement is:DROP VIEW <view name> [,<view name>...[,<last view name>]]As such, to remove a VIEW named VW_SALES_PRODUCTION from the database, you would execute the SQL statement:DROP VIEW vw_sales_productionYou can remove several views at once by separating the names of the views with commas in a single DROP VIEW statement. For example, to remove views VW_SALES_PRODUCTION_EAST and VW_SALES_PRODUCTION_WEST, you would use the SQL statement:DROP VIEW

vw_sales_production_east, vw_sales_production_west

Although no data is erased when you drop a view, you do need to make sure that no stored procedures or other views reference the view you are about to drop. If you run a stored procedure or use view that references a dropped view, the DBMS will respond with an error message in the form:Server: Msg 208, Level 16, State 1, Procedure

<name of referencing view>, Line 2

Invalid object name '<name of dropped view>'.

Server: Msg 4413, Level 16, State 1, Line 1

Could not use view '<name of referencing view>' because of

previous binding errors.

(The DBMS, of course, substitutes the actual name of the dropped view for "<name of dropped view>" and the actual name of the view that references the dropped view for "<name of referencing view>." )