Views in SQL Server

Getting Information on a View

SQL Server stores information on the view in the following system tables:

• SYSOBJECTS — stores the name of the view.
• SYSCOLUMNS — stores the names of the columns defined in the view.
• SYSDEPENDS — stores information on the view dependencies.
• SYSCOMMENTS — stores the text of the view definition.

There are also certain system-stored procedures that help retrieve information on views. The sp_help system-stored procedure displays view-related information. It displays the view definition, provided the name of the view is given as its parameter.

Example

Sp_helptext vwCustomer

Displays the definition of the vwCustomer view.

Note

If a view is created with the WITH ENCRYPTION option, it cannot view the sp_helptext system-stored procedure.

Altering Views

You can modify a view without dropping it. This ensures that the permission on the view is also not lost. You can modify a view without affecting its dependent objects, such as triggers and stored procedures.

You modify a view using the ALTER VIEW statement.

Syntax

ALTER VIEW view _name [column_ name)]
[WITH ENCRYPTION]
AS select_statement
[WITH CHECK OPTION]

Where:

view_name is the view to be altered.

column_name specifies the name of the column to be used in the view. If the column_name option is not specified then the view is created with the same column as specified in the select_statement.

WITH ENCRYPTION encrypts the text for the view in the SYSCOMMENTS tables.

AS specifies the action that will be performed by the view.

select_statement specifies the SELECT statement that defines a view. The view could use other views and tables.

WITH CHECK OPTION forces the data modification statements to follow the criteria given in the SELECT statement definition.

Example

ALTER VIEW vwCustomer
AS
SELECT CustomerId, Company Name, Phone, Fax
FROM Customers

Alters the vwCustomers view to add the Fax column of the Customers table.

When you query on the view using

SELECT *FROM vwCustomer

You see the following output:

(991 rows affected)

Note

If you define a view, a SELECT* statement, and then the structure of the underlying tables by adding columns, the new columns do not appear in the view. When all columns are selected in a CREATE VIEW statement, the column list is interpreted only when you first create a view. To see the new columns in the view, you must alter the view.