Precautions for deleting a view

When you delete a view, you remove the view definition and other information about the view from the system directory. All permissions for the view will also be removed.

Any VIEW on a TABLE that is dropped using DROP TABLE must be explicitly dropped using DROP VIEW.

When you execute DROP VIEW on an indexed VIEW, all indexes on the VIEW are automatically dropped. To display all indexes on a view, use SP_helpIndex.

When querying through a view, the database engine checks to ensure that all database objects referenced in the statement exist, that they are valid in the context of the statement, and that the data modification statement does not violate any data integrity rules. If the check fails, an error message is returned. If the check succeeds, the operation is converted to an operation on the underlying table. If the underlying table or view has changed since the view was originally created, it may be useful to delete and recreate the view.

For more information about determining the dependencies of a particular view, see sys.SQL_dependencies (Transact-SQL).

For more information about viewing view text, see SP_helptext (Transact-SQL).

Deleting a view requires CONTROL permission on the view, ALTER permission on the schema that contains the view, or membership in the DB_DDladmin fixed server role.

Delete the view using the SSMS database management tool

1, connect database, select database, expand database – “expand view -” select the view to delete – “right click -” select Delete.

2. In the Delete Object pop-up box -, click OK.

3. Delete results.

Use t-SQL scripts to delete views

Grammar:

Declare database references

Use database name;

go

— Determines whether the view exists and deletes it if it does

If exists(select * from sys.views where name=[schema name][.] view name)

Delete a single view

Drop view [schema name][.

Delete multiple views

— Drop view [schema name][.] View name 1,[.] View name 2,…… ;

go

Example:

Declare database references

use testss;

go

— Determines whether the view exists and deletes it if it does

if exists(select * from sys.views where name=’view1′)

Delete a single view

drop view view1;

go

Example results: