information_schema.tables sysobjects View names information_schema.view_table_usage sysdepends Base object names information_schema.views syscomments View definition
information_schema.view_column_usage syscolumns Columns defined in a view
y
To view a list of views defined in a database, you can use the following syntax:
SELECT *
FROM information_schema.tables WHERE table_type = `view'
To view the SELECT statement that makes up a view, use the sp_helptext stored procedure. Use the following syntax:
sp_helptext view_name
Preventing users from displaying view definitionsSQL Server stores a view's definition in the syscomments table; however, you should not delete the definition from this table as a technique for hiding the view definition. Instead, you should use encryption. (Although you can delete the definition of the view and it will still work, Microsoft recommends that you not delete it from syscomments to avoid problems when you upgrade to future versions of SQL Server.)
You can optionally add the WITH ENCRYPTION operator to prevent users from reading a view's definition, as follows:
CREATE VIEW view_name WITH ENCRYPTION AS
select_statement
Modifying a view
You can alter a view by either dropping and re-creating it, or by using the ALTER VIEW statement. If you drop a view, you must create any permissions assignments when you re-create the view. In contrast, if you change a view by using the ALTER VIEW statement, the view retains whatever permissions you had assigned to your users. You can use the following syntax to change an existing view:
ALTER VIEW view_name (column_list)
AS
select_statement
If you created the view with the WITH ENCRYPTION operator, you must include that option in the ALTER VIEW statement.
Dropping a view
You drop a view by using the DROP VIEW statement. When you delete a view, SQL Server automatically deletes the view definition and any permissions you have assigned to users for it. If you delete a table that is referenced by a view, SQL Server does not automatically drop the view.
Thus, you must manually drop the view. You can use the sp_depends stored procedure to determine if a table has any dependent views by using the following syntax:
sp_depends object_name
You must be the owner of a view to delete it. However, if you are a member of the sysadmins server role or the database owner database role, you can drop a view that is owned by another user by specifying the owner's name in the DROP VIEW statement.
Use the following syntax to delete a view:
DROP VIEW [owner.]view_name
Using views to work with data
You can insert, update, and delete rows from a table by using a view. Note that views do not contain the actual data in a table-instead, views are simply windows to the data in the table. Note:
If you have configured any of the columns in the tables on which the view is based to not permit nulls, and these columns are not contained in the view, you will not be able to insert rows into the table. Depending on the update statement, you might not be able to change the table either.
You cannot modify the data in more than one table through a view. If a view is based on joined tables, you can modify the data in only one of the joined tables, not both. If you want to modify the data in both tables on which a view is based, you will need to write separate statements for modifying the data in each table.
Because views are essentially windows to your tables, you cannot insert, update, or delete rows if your statements will violate data integrity. For example, the rental and rental_detail tables in the
movies database are linked together in a primary key to foreign key relationship based on the invoice_num column. Thus, you cannot change a value in the invoice_num column in either table, nor can you change it through a view.
Use the following syntax to insert data into a table by using a view:
INSERT INTO view_name VALUES(value_list)
Replace the value_list with a list of values you want to insert into the columns contained in the view.
Use the following syntax to update data through a view:
UPDATE view_name SET column_name = value WHERE condition
Likewise, you can use the following syntax to delete rows through a view:
DELETE FROM view_name WHERE condition
±
1. What are some advantages of using views?³
Views help provide additional security as you can give your server's users permissions only to the view itself and not the underlying data.With views you can restrict users from seeing sensitive information. Views can also be used as a way to hide a complex database design.±
2. You would like to prevent anyone from reading the statement you used to build a view.What should you do?
³
You can prevent users from displaying a view definition by encrypting it. You encrypt a view definition by adding the WITH ENCRYPTION clause after the CREATE VIEW statement as follows:CREATE VIEW view_name WITH ENCRYPTION AS
SELECT statement