Finding invalid objects
Published 29 October 2019
You can use SQL Prompt to find invalid objects in your database. This can be useful, for example, if you have inherited a legacy database and you want to know if any objects are referencing objects that have been dropped and no longer exist in the database.
Objects are listed as invalid by SQL Prompt if they:
- can't be compiled
- can be compiled, but can't be executed
For example, a stored procedure that references an object that no longer exists in the database is listed as invalid.
WARNING: synonyms with not existing reference are not supported.
To find invalid objects in a database:
- In the Object Explorer, right-click the database you want to find invalid objects in, and click Find Invalid Objects.
The Invalid Objects tab is shown:
Invalid objects are listed in the upper pane.
- Click an object in the list to view its SQL creation script in the lower pane.
To open the script as an ALTER statement in a new query window, click Script selected objects as ALTER.
script multiple invalid objects as ALTER by selecting more than one object in the upper pane, and then clicking Script selected objects as ALTER:
This only creates one new query. Each object is scripted as a separate ALTER action.
locate an invalid object in the Object Explorer by double-clicking it in the upper pane:
copy a list of invalid objects you select in the upper pane by right-clicking a selected object, and clicking Copy:
- copy any SQL you select in the lower pane by pressing Ctrl + C.