Contents

How to find invalid objects in a SQL Server database

SQL Complete makes it easy to find invalid objects in multiple databases. This feature helps to learn if any objects reference other objects that no longer exist.

To find invalid objects in a database:

1. Open the Find Invalid Objects window by using one of the following ways:

• Navigate to the SQL Complete and select Find Invalid Objects.
• In Object Explorer, right-click the required database, and select SQL Complete > Find Invalid Objects.
• Press Ctrl + Alt + F.

2. Specify the connection and select the databases to search for invalid objects.

3. Click Analyze to start searching.

Handle invalid objects

Invalid objects are displayed in a grid that includes a type of the database object, the schema it belongs to, the name of the invalid object, and the reason why it is deemed invalid.

In the Find Invalid Objects window, you can perform the following tasks:

• Select the invalid objects to view the SQL script generated for the object at the bottom pane.

• Right-click the invalid objects and select on the shortcut menu:

  • Clear All to remove all invalid objects from the grid.
  • Find in Object Explorer to locate the selected invalid object in Object Explorer.
  • Export to CSV to store data of the selected invalid object in the CSV file format.

• Generate the ALTER and DROP scripts for the invalid objects to a new SQL document or to the clipboard.

To script out the object:

1. In the Find Invalid Objects grid, select the objects, click Script as ALTER or Script as DROP.

Note

If you want to script an object to clipboard, click the down arrow next to Script as ALTER or Script as DROP and select To Clipboard.

1. In a new SQL query document, the generated script will display a comment next to the invalid object.

Troubleshooting

You may face the following error while looking for invalid objects in databases with more than 5,000 objects:

Could not find a part of the path 'C:\Users\User1\AppData\Local\Temp\Devart\691f7f39ac7f43da86.tmp'

The root cause of this issue is the restricted temporary file usage.

Possible reason	How to fix
There was not enough space on the disk C:\ to create temporary files.	Create the C:\Users%WinUser%\AppData\Local\Temp\Devart folder manually.
Saving the temporary file might have failed due to timeout caused by antivirus.	Disable antivirus.
Restricted access to the Devart folder for the user that launches SSMS.	Make sure all the users have both Read and Write permissions.

Want to Find out More?

• Previous
• Next