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:
- Analyzing the algorithm of deleting lost transactions
- Creating a stored procedure using CRUD
- Testing the stored procedure and turning it into a script
- Reviewing a number of useful settings that vastly help in the workflow
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:
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:
Pic.2. Setting up and running the search for references to invalid objects.
Now, the process of searching for references to invalid objects starts:
Pic.3. The search process
After the process is completed, you will see a table containing all objects that refer to invalid objects:
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:
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:
Pic.6. The code analysis result
Summary
In this final part, 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.
