ALTER VIEW
Alters a previously created view (created by executing CREATE VIEW), including indexed views, without affecting dependent stored procedures or triggers and without changing permissions. For more information about the parameters used in the ALTER VIEW statement, see CREATE VIEW.
Syntax
ALTER VIEW [ < database_name > . ] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]
[ WITH < view_attribute > [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]
< view_attribute > ::=
{ ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
Arguments
view_name
Is the view to change.
column
Is the name of one or more columns, separated by commas, to be part of the given view.
Important Column permissions are maintained only when columns have the same name before and after ALTER VIEW is performed.
Note In the columns for the view, the permissions for a column name apply across a CREATE VIEW or ALTER VIEW statement, regardless of the source of the underlying data. For example, if permissions are granted on the title_id column in a CREATE VIEW statement, an ALTER VIEW statement can rename the title_id column (for example, to qty) and still have the permissions associated with the view using title_id.
n
Is a placeholder indicating the column can be repeated n number of times.
WITH ENCRYPTION
Encrypts the syscomments entries that contain the text of the ALTER VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
SCHEMABINDING
Binds the view to the schema. When SCHEMABINDING is specified, the select_statement must include the two-part name (owner.object) of tables, views, or user-defined functions referenced.
Views or tables participating in a view created with the schema binding clause cannot be dropped unless that view is dropped or changed so it no longer has schema binding. Otherwise, SQL Server raises an error. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
VIEW_METADATA
Specifies that SQL Server will return to the DBLIB, ODBC, and OLE DB APIs the meta data information about the view, instead of the base table or tables, when browse-mode meta data is being requested for a query that references the view. Browse-mode meta data is additional meta data returned by SQL Server to the client-side DB-LIB, ODBC, and OLE DB APIs, which allow the client-side APIs to implement updatable client-side cursors. Browse-mode meta data includes information about the base table that the columns in the result set belong to.
For views created with VIEW_METADATA option, the browse-mode meta data returns the view name as opposed to the base table names when describing columns from the view in the result set.
When a view is created WITH VIEW_METADATA, all its columns (except for timestamp) are updatable if the view has INSERT or UPDATE INSTEAD OF triggers. See Updatable Views in CREATE VIEW.
AS
Are the actions the view is to take.
select_statement
Is the SELECT statement that defines the view.
WITH CHECK OPTION
Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement defining the view.
Remarks
For more information about ALTER VIEW, see Remarks in CREATE VIEW.
Note If the previous view definition was created using WITH ENCRYPTION or CHECK OPTION, these options are enabled only if included in ALTER VIEW.
If a view currently in use is modified by using ALTER VIEW, Microsoft® SQL Server™ takes an exclusive schema lock on the view. When the lock is granted, and there are no active users of the view, SQL Server deletes all copies of the view from the procedure cache. Existing plans referencing the view remain in the cache but are recompiled when invoked.
ALTER VIEW can be applied to indexed views. However, ALTER VIEW unconditionally drops all indexes on the view.
Permissions
ALTER VIEW permissions default to members of the db_owner and db_ddladmin fixed database roles, and to the view owner. These permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with SELECT permission on the tables, views, and table-valued functions being referenced in the view, and EXECUTE permission on the scalar-valued functions being invoked in the view.
In addition, to alter a view WITH SCHEMABINDING, the user must have REFERENCES permissions on each table, view, and user-defined function that is referenced.
Examples
A. Alter a view
This example creates a view that contains all authors called All_authors. Permissions are granted to the view, but requirements are changed to select authors from Utah. Then, ALTER VIEW is used to replace the view.
-- Create a view from the authors table that contains all authors.
CREATE VIEW All_authors (au_fname, au_lname, address, city, zip)
AS
SELECT au_fname, au_lname, address, city, zip
FROM pubs..authors
GO
-- Grant SELECT permissions on the view to public.
GRANT SELECT ON All_authors TO public
GO
-- The view needs to be changed to include all authors
-- from Utah.
-- If ALTER VIEW is not used but instead the view is dropped and
-- re-created, the above GRANT statement and any other statements
-- dealing with permissions that pertain to this view
-- must be re-entered.
ALTER VIEW All_authors (au_fname, au_lname, address, city, zip)
AS
SELECT au_fname, au_lname, address, city, zip
FROM pubs..authors
WHERE state = 'UT'
GO
B. Use @@ROWCOUNT function in a view
This example uses the @@ROWCOUNT function as part of the view definition.
USE pubs
GO
CREATE VIEW yourview
AS
SELECT title_id, title, mycount = @@ROWCOUNT, ytd_sales
FROM titles
GO
SELECT *
FROM yourview
GO
-- Here, the view is altered.
USE pubs
GO
ALTER VIEW yourview
AS
SELECT title, mycount = @@ ROWCOUNT, ytd_sales
FROM titles
WHERE type = 'mod_cook'
GO
SELECT *
FROM yourview
GO