Find invalid objects in SQL Server databases

July 6th, 2020

In the course of time, we can experience situations when some certain database objects have not been distributed on one or several databases or database instances. This can happen for a number of reasons. An example of that would be a case when a stored procedure refers to a nonexistent table. In this article, we are going to take a closer look at the issue of how to find invalid objects in SQL Server databases with the help of dbForge SQL Complete.

To have a much more complete insight into the functionality of this tool, you will first need to consider the previous series of our articles. As stated in the first part, this tool allows us to find and delete incomplete SQL Server transactions, but not only that. In addition to that, IntelliSense-style code completion, highly customizable and shareable code-formatting, efficient code refactoring are among its advantages.

This article is going to be the final part of the series of Deleting Lost Transactions in MS SQL Server articles. So let’s go over the topics we have discussed and review what we have accomplished in the four previous articles:

Finding invalid objects

Apart from its other functions, with the help of the SQL Complete tool, we can find invalid objects in SQL Server. To do that, first, select the SQL Complete\Find Invalid objects command in the main menu:

Select the command 'Find Invalid Objects' in the menu of SQL Complete

Pic.1. Choosing the “Find Invalid Objects” command in the SQL Complete main menu

Once it’s done, in the window that opens, we select the required databases from MS SQL Server instances and press “Analyze” at the upper right corner or at the middle of the screen:

Select the database we need and start analyzing

Pic.2. Setting up and running the search for references to invalid objects.

Now, the process of searching for references to invalid objects starts:

The search for invalid objects begins

Pic.3. The search process

After the process is completed, you will see a table containing all objects that refer to invalid objects:

The table with objects that have a reference to invalid objects in a database

Pic.4. The table showing all objects that refer to invalid database objects

As you can see here, the table displays the type, schema, and name of the object that refers to the invalid object, the name of which is also displayed. At the bottom, you can see the code of the object that refers to the invalid object. In this case, the DeleteArchive stored procedure refers to the nonexistent srv.KillSession table, which is an unresolved reference to the object.

After that, you can proceed to the location of the stored procedure, by right-clicking on it and selecting “Find in Object Explorer” in the context menu:

Select the 'Find in Object Explorer' option in the table


Pic.5. Finding the object that refers to an invalid database object

Finally, it will be enough to analyze the code of the stored procedure, which refers to the nonexistent table, and decide whether you need to create the srv.KillSession table:

The result shows that none of the invalid objects were found in the database

Pic.6. The code analysis result

Summary

In this final part of articles, we took you through the whole process of finding invalid database objects, providing a step-by-step guide of how to accomplish that using the SQL Complete tool. The result leaves the user with a decision whether you create the nonexistent object or change the reference to the existing one.

Evgeniy Gribkov

Latest posts by Evgeniy Gribkov (see all)

Leave a Comment