Find invalid objects in your databases

DBA has a number of duties that are primarily targeted at supporting of database performance capabilities and data consistency. The administrator can use the CHECKDB command to easily verify the data consistency; however, in case they need to find an invalid object in a database schema, some difficulties may occur.

ORACLE for instance, allows you to get a list of invalid objects:

SQL Server doesn’t allow to do that directly. In most cases, you need to execute a script to see that an object is invalid. This is very inconvenient…
So let’s create a script that will search invalid objects:

The script is useful for the primary analysis. However, there are some gaps in it. The main problem is that the script does not show objects with invalid columns or parameters.

We will get an error while executing the storage procedure:

Moreover, the script will not work on SQL Server 2005. So, we can’t use the provided script as the primary one.

However, SQL Server contains the sp_refreshsqlmodule system procedure. The procedure updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

Thus, the sp_refreshsqlmodule procedure generates an error if an object contains invalid columns or properties. The procedure can be called inside a cursor for each object. If no invalid objects exist, then the procedure completes with no errors.

It is important to remember, however, that script objects may have no dependencies or can contain no invalid objects initially. There is no expediency to verify such objects. SQL Server takes care of that.

The following script can be used for searching invalid objects:

The same script for SQL Server 2005:

Script execution results are as follows (for a test database):

SQL Server doesn’t check an object’s name while creating a synonym. So, a synonym can be created for a non-existing object.

To find all invalid synonyms, the following script can be used:

If there is need to add this check to a current script:

As you can see, metadata way you can extend the standard functionality of SSMS to do your day-to-day database tasks.

4 Responses to “Find invalid objects in your databases”

  1. Tola Says:

    Good script. There is a mistake IF XACT_STATE() 0 should be IF XACT_STATE() 0.
    The script to find all invalid synonyms gave me this error though “Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.”

  2. Sergey Syrovatchenko Says:

    Tola, thanks for the note. Try to add COLLATE DATABASE_DEFAULT

  3. M. Lembke Says:

    Thanks for this great article.

    there is a little mistake in the script.

    Wrong
    IF XACT_STATE() 0
    ROLLBACK TRANSACTION
    Correct
    IF XACT_STATE() = 0
    ROLLBACK TRANSACTION

  4. Sergey Syrovatchenko Says:

    Done. M. Lembke, thanks for your note.

Leave a Comment