Identifying altered views requiring manual recompile [CR #633463]

This section was added to SQL Statements > ALTER VIEW Statement:

Check for, and correct, any dependent views that become invalid due to changes to their underlying tables.

Under most circumstances the database server automatically recompiles views to keep them valid if the underlying tables change. However, if your table alteration removes or materially changes something referenced by the view definition, then the dependent view becomes invalid. For example, if you remove a column referenced in the view definition, then the dependent view is no longer valid. Correct the view definition and manually recompile the view.

1. Run sa_dependent_views to get the list of dependent views.

2. Perform the DDL operation that alters the table. The server automatically disables dependent views, and attempts to recompile them once the DDL is complete.

3. Check that all the views listed by sa_dependent_views are valid. For example, perform a simple test such as SELECT * FROM myview.

4. If a view is invalid, it is likely you will need to alter the view definition to resolve the issue. Examine the view definition against the DDL change that you made and make the necessary changes. Run ALTER VIEW RECOMPILE to correct the view definition.

5. Test the corrected view to make sure it works. For example, perform a simple test such as SELECT * FROM myview.

For detailed information on how the database server handles view dependencies, see SQL Anywhere Server – SQL Usage > Creating Databases > Working with database objects > Working with views > View Dependencies.