You can alter a view using Sybase Central or Interactive SQL.
In Sybase Central, you can alter the definition of views, procedures, and functions on the object's SQL tab in the right pane. You edit a view in a separate window by selecting the view and then choosing File > Edit In New Window. In Interactive SQL, you can use the ALTER VIEW statement to alter a view. The ALTER VIEW statement replaces a view definition with a new definition, but it maintains the permissions on the view.
You cannot rename an existing view directly. Instead, you must create a new view with the new name, copy the previous definition to it, and then drop the old view.
For information on altering database objects, see Setting properties for database objects.
For information on setting permissions, see Granting permissions on tables and Granting permissions on views.
For information on revoking permissions, see Revoking user permissions.
If you want to alter the definition for a view, and there are other views dependent on the view, there may be additional steps to make after the view alteration is complete. For example, after you alter a view, the database server automatically recompiles it, enabling it for use by the database server. If there are dependent views, the database server disables and re-enables them as well. If they cannot be enabled, they are given the status INVALID and you must either make the definition of the view consistent with the definitions of the dependents views, or vice versa.
To determine whether there are views dependent on a view, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.
For information on how view alterations affect view dependencies, see View dependencies.
Connect to the database as the DBA or as the owner of the view.
Open the Views folder.
Select the desired view.
In the right pane, click the SQL tab and edit the view's definition.
TipIf you want to edit multiple views, you can open separate windows for each view rather than editing each view on the SQL tab in the right pane. You can open a separate window by selecting a view and then choosing File > Edit In New Window from the popup menu. |
From the File menu, select Save.
Connect to the database as a DBA user or as the owner of the view.
Execute an ALTER VIEW statement.
This example shows that when you are changing schema-related aspects of the view, you are effectively replacing the definition of the view. In this case, the view definition is being changed to have column names that are more informative.
CREATE VIEW DepartmentSize ( col1, col2 ) AS SELECT DepartmentID, COUNT( * ) FROM Employees GROUP BY DepartmentID; ALTER VIEW DepartmentSize ( DepartmentNumber, NumberOfEmployees ) AS SELECT DepartmentID, COUNT( * ) FROM Employees GROUP BY DepartmentID;
The next example shows that when you are only changing an attribute of the view, you do not need to redefine the view. In this case, the view is being set to have its definition hidden.
ALTER VIEW DepartmentSize SET HIDDEN;
For more information altering views, see ALTER VIEW statement.