How to find invalid objects in a SQL Server database

SQL Complete makes it easy to find invalid objects in multiple databases using a GUI interface or the command line. This feature helps to learn if any objects reference other objects that no longer exist.

The guide describes the following how-to topics:

How to find invalid objects in a database

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

  • On the ribbon, select SQL Complete > 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.

How to manage invalid objects

Invalid objects are listed in a grid showing the type of database object, the schema it belongs to, its name, and why it is deemed invalid. The preview pane displays the SQL script generated for the invalid object and highlights the object that caused the selected object to be invalid.

Find invalid objects panes

Note

Searching for invalid objects is not supported on Azure SQL servers.

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

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

  • Right-click the invalid object 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 all invalid objects as a grid in the CSV file. The grid contains the information available in the Find Invalid Objects grid.
  • Generate the ALTER and DROP scripts for the selected invalid objects to a new SQL document or to the clipboard.
  • Create a .bat file to automate searching invalid objects from the command line.

How to export invalid objects to a file

1. Find invalid objects in the required database.

2. In the Find Invalid Objects grid, right-click the objects and select Export to CSV.

Generate the ALTER and DROP scripts

3. In the Save As window that opens, specify the file name and path to save the file, and click Save.

The file will contain all invalid objects exported from the Find Invalid Objects grid:

CSV file

How to script out the object

1. In the Find Invalid Objects grid, select the checkbox next to the required object.

2. On the toolbar, click Script as ALTER to generate the ALTER script for the object or Script as DROP to generate the DROP script for the object.

Note

To copy the script to the clipboard, click the down arrow next to Script as ALTER or Script as DROP and select To Clipboard.

Script as ALTER

3. By default, the script will open in a new SQL document.

Invalid column name

How to create a .bat file

SQL Complete has a built-in Command Line Wizard that converts the search options for invalid objects into command-line syntax and saves it as a *.bat file. You can then use this .bat file with a script and Windows Task Scheduler or any other task scheduler tool to schedule the search for invalid objects.

To open the Command Line Wizard, click Save Command Line on the toolbar.

Command Line wizard

Options of the Command Line Wizard

The command line wizard has the following options:

Option Description
Connection Specify the connection string for the database to search for invalid objects.
Password Specify the server password. Note that it will override the values specified in the Connection parameter.
Database Specify the database to find invalid objects. You can specify either a single database or multiple databases separated by commas. Note that it will override the database specified in the Connection parameter.
All databases Select this option if you want to search for invalid objects in all non-system databases located on the server.
Report Specify a path to the report that will be generated in the CSV file format.
Log Specify a path to a log file.
Echo OFF Select this option to enclose all the text in the bat file in the @Echo OFF … @Echo ON construct. This will turn off the display of the commands being entered on the screen.
Keep opened Select this option to place the pause command at the end of the bat file text. This will leave the command window open.
PowerShell Select this option to generate the & symbol at the beginning of the bat file text. This will ensure the PowerShell compatibility.

You can click Validate to verify the script.

How to find invalid objects in a database from the command line

You can automate the search for invalid objects in a database using the following script from the command line:

sqlcomplete.com /findinvalidobjects [/option_name1[:value | [parameter1:value parameter2:value ..]] /option_name2 ..]

Command line usage

Argument Action and usage
/findinvalidobjects Finds any objects referencing other objects that were dropped and no longer exist in the database.
/connection Usage: /connection:<connection_string>
Specifies the connection string.
/password Usage: /password:<password>
Specifies the server password and overrides the values specified in the /connection parameter.
/database Usage: /database:<dbname1[,dbname2]>
Specifies the database(s) and overrides the values specified in the /connection parameter. You can specify either a single database or multiple databases separated by commas.
/alldatabases Usage: /alldatabases
Finds invalid objects for all non-system databases specified in the /connection parameter.
/exitcodes Usage: /exitcodes
Displays the list of exit codes that the command-line operation can return.
/log Usage: /log:<filepath>
Generates a log file and saves it to the specified folder.
/report Usage: /report:<filename>Generates a report in the CSV file format.
/treatwarningaserror Usage: /treatWarningAsError:[Yes|No]
Specifies the behavior for treating warnings.
Yes: Treat all warnings as errors. The errormode option will determine the subsequent behavior.
No: Do not consider warnings to be errors and continue the command line execution.
/errormode Usage: /errormode:<ignore|abort>
Specifies the application behavior when encountering an error:
Ignore: Ignore all errors and continue execution.
Abort: Cancel execution if an error occurs.

Exit codes used in the command line

An exit code is a status code that a command-line script returns. It indicates whether the execution was successful or if an error occurred. When a user runs the /findinvalidobjects command, the script may return the following exit codes:

Exit code Description Additional information
0 Success The operation has been completed without errors.
1 Unhandled exception Unhandled exception. See the log for more details.
10 Command line usage error The command line was used incorrectly. For example, an incorrect flag or incorrect syntax may have been used.
11 Illegal argument duplication Some arguments may not appear in a command line more than once. For example, /arg2 depends on /arg1 but you have specified /arg2 without specifying /arg1, or /arg2 cannot be used with /arg1, but you have used them both.
20 Trial expired Software trial period has expired, or the product has not been activated.
40 Server connection fail Server connection failed.
104 High-level parser error High-level errors were encountered while parsing a scripts folder.
105 Resource unavailable The file is missing.

Worked example: How to find invalid objects and create a CSV report

Let us find invalid objects in the AdventureWorks2022 database on the demo-mssql\SQLEXPRESS server and generate a report in the CSV file format.

To find invalid objects:

1. Open the Command Prompt or terminal and navigate to the installation folder of dbForge SQL Complete. For this, you can use the cd command. The default installation folder is C:\Program Files (x86)\Devart\dbForge SQL Complete.

2. Enter the following command:

sqlcomplete.com /findinvalidobjects /connection:"Data Source=demo-mssql\SQLEXPRESS; Integrated Security=False; User ID=yourUsername"; /database:AdventureWorks2022 /log:"D:\logfile.txt" /report:"D:\report.csv"

where:

  • AdventureWorks2022 is a name of the database whose invalid objects you want to find.
  • D:\logfile.txt is a path to the log file the operation will create after completion.
  • D:\report.csv is a path to the report file the operation will generate after completion.

3. Press Enter to execute the command.

The command outputs a table with invalid objects and generates a log and report files.

Result

Log file

Log file

Report

Report

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 who launches SSMS. Make sure all the users have both Read and Write permissions.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge SQL Complete for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the SQL Complete in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the SQL Complete for your business, request a demo to see it in action.
Ready to start using dbForge SQL Complete for SQL Server?
Download a free 14-day trial