Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

DROP VIEW

SQL Server 2000

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

See Also

ALTER VIEW

CREATE VIEW

syscolumns

syscomments

sysdepends

sysobjects

sysprotects

USE

Show:
© 2014 Microsoft