SQL Server: Alter or Delete Views

In SQL Server, a view can be modified either by using SQL Server Management Studio or using TSQL

User the ALTER VIEW statement to modify a query of a view.

Example: Modify View

ALTER VIEW dbo.EmployeeAddress  
AS 
    SELECT emp.FirstName, emp.LastName, emp.HireDate, addr.Address 
    FROM Employee emp JOIN Address addr 
    on emp.EmployeeID = addr.EmployeeID
    WHERE emp.HireDate > '01/01/2010'

Modify View Using SQL Server Management Server

Step 1: Open SSMS and connect to the database with the view to be modified

Step 2: In the Object Explorer, expand the database. Expand the views folder.

Step 3: Right-click on the view to be modified and click Design.

Step 4: In the diagram pane of the query designer, make modifications to the view by adding or deleting columns/elements from the View; Select additional columns from another table.

Step 5: Click Save to save the changes.

Delete Views in SQL Server

A view in SQL Server can be deleted (Drop) using SQL Server Management Studio or T SQL.

Use DROP VIEW statement to delete a view, as shown below:

Example: Delete Views

DROP VIEW dbo.EmployeeAddress;

Check the existance of a view using IF condition, if you are not sure whether a view exists or not.

Example: Delete Views

IF OBJECT_ID('dbo.EmployeeAddress', 'V') IS NOT NULL  
    DROP VIEW dbo.EmployeeAddress;

Note: When you drop a view, the definition of the view and other information about the view is deleted from the system catalog.

Delete Views using SQL Server Management Studio

Step 1: Open SSMS, connect to the database having the view to be modified.

Step 2: In the Object Explorer, expand the database. Expand Views folder.

Step 3: Right-click on the view name to be deleted and select Delete.

Step 4: In the Delete Object dialog box, click OK.