DROP VIEW
Removes one or more views from the current database. DROP VIEW can be executed against indexed views.
Syntax
DROP VIEW { view } [ ,...n ]
Arguments
view
Is the name of the view(s) to be removed. View names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the view owner name is optional. To see a list of currently created views, use sp_help.
n
Is a placeholder indicating that multiple views can be specified.
Remarks
When you drop a view, the definition of the view and other information about the view is deleted from the sysobjects, syscolumns, syscomments, sysdepends, and sysprotects system tables. All permissions for the view are also deleted.
Any view on a dropped table (dropped by using the DROP TABLE statement) must be dropped explicitly by using DROP VIEW.
When executed against an indexed view, DROP VIEW automatically drops all indexes on a view. Use sp_helpindex to display all indexes on a view.
When querying through a view, Microsoft® SQL Server™ checks to make sure that all the database objects referenced anywhere in the statement exist, that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. A check that fails returns an error message. A successful check translates the action into an action against the underlying table(s).
If the underlying table(s) or view(s) have changed since the view was originally created, it may be useful to drop and re-create the view.
For more information about determining dependencies for a specific view, see sp_depends.
For more information about viewing the text of the view, see sp_helptext.
Permissions
DROP VIEW permissions default to the view owner, and are not transferable. However, members of the db_owner and db_ddladmin fixed database role and sysadmin fixed server role can drop any object by explicitly specifying the owner in DROP VIEW.
Examples
This example removes the view titles_view.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'titles_view')
DROP VIEW titles_view
GO