DROP VIEW (T-SQL)

Removes one or more views from the current database. Any view on the dropped table (dropped by using the DROP TABLE statement) must be explicitly dropped by using DROP VIEW.

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.

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 permission defaults to the view owner, and is not transferable. However, members of the db_owner fixed database role or 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

See Also