{{announcement.body}}
{{announcement.title}}

Find Invalid Objects in SQL Server Databases

DZone 's Guide to

Find Invalid Objects in SQL Server Databases

In this article, we are going to take a closer look at the issue of how to find invalid objects in SQL Server databases.

· Database Zone ·
Free Resource

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. This piece is going to be the final thought closing 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 previously:

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:

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:

Setting up and running the search for references to invalid objects

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

The search process

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

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:

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 code analysis result

Summary

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

Topics:
database, database administration, databases, how to find invalid objects, invalid objects, sql complete, sql server, tutorial

Published at DZone with permission of Evgeniy Gribkov . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}