Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

ALTER VIEW

SQL Server 2000

  Topic last updated -- June 2007

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

Indicates that SQL Server will convert the original text of the view definition to an obfuscated format. Note that obfuscated views can be reverse engineered because SQL Server must de-obfuscate views for execution. In SQL Server 2000, the obfuscated text is visible in the syscomments system table and may be susceptible to de-obfuscation attempts.

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

See Also

CREATE TABLE

CREATE VIEW

DROP VIEW

Programming Stored Procedures

SELECT

Using Identifiers

Show:
© 2014 Microsoft