An Engineers Notebook

SQL: Alter Views

In this example, we alter a view so that instead of dislpaying all colums in the Employees table, we now only show the EmployeeID, FirstName and LastName. This is specified under the “SELECT DISTINCT” part.

Here is the View before the following script is executed.

Alter table - Table before script executed
See Creating Views to see the view created before this was run.


USE TechShizz
GO

ALTER VIEW vEmployeesWithSales
AS

    SELECT DISTINCT
        Employees.EmployeeID,
        FirstName,
        LastName
    FROM
        Employees
            JOIN
        Sales ON Employees.EmployeeID =Sales.EmployeeID

GO


Next we can re-name the view by using the following command:

sp_rename ‘vEmployeesWithSales’,’Employees’

Once renamed and altered the table should look like this once executed:

Alter table - Table after script executed


Another Example

We could alter the view so that we have a view for the entire Emplyees table.
USE TechShizz
GO

ALTER VIEW vEmployees
AS

    SELECT DISTINCT
        *
    FROM
        Employees
GO

Alter table - Table after 2nd script executed

Leave a Reply

Your email address will not be published.