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.

Example

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:

CutomerId

Company Name

Phone, Fax

ALFKI

Alfreds Futterkiste

030-0074321, 030-0076545

ANTON

Antonio Moreno Taqueria

(5)555-3932 NULL

AROUT

Around the Horn

(171)555-7788, (171)555-6750

(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.