Finding invalid objects - SQL Prompt

SQL Prompt enables you 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:

cannot be compiled
can be compiled, but cannot be executed
For example, a stored procedure that references an object that no longer exists in the database is listed as invalid.

To find invalid objects in a database:

In the SQL Server Management Studio Object Explorer, right-click the database you want to find invalid objects in, and click Find Invalid Objects.
The SQL Prompt tab is displayed:

Invalid objects are listed in the upper pane. You can 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.

Tips

You can:

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 seperate 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.