Identifying and Fixing Invalid Dependent Views
Check for, and correct, any dependent views that become invalid due to changes to their underlying tables.
Context
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.
Procedure
- Run sa_dependent_views to get the list of dependent views.
- Perform the DDL operation that alters the table. The server automatically disables dependent views, and attempts to recompile them once the DDL is complete.
- Check that all the views listed by sa_dependent_views are valid. For example, perform a simple test such as SELECT * FROM myview.
- 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.
- Test the corrected view to make sure it works. For example, perform a simple test such as SELECT * FROM myview.
Results
The sa_dependent_views system procedure returns the list of all dependent views for a given table or view.