Finding Invalid Objects

The term invalid object is a database object referencing another object or objects that no longer exist in a database. For example, a stored procedure refers to a table deleted from the database. In this case, a stored procedure is an invalid object.

In dbForge Studio, you can use the Find Invalid Objects feature to search through a MySQL database schema to identify and recompile invalid objects, such as functions, procedures, triggers, and views.

The guide provides step-by-step instructions for the following topics:

Find invalid objects in a database

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

  • In Database Explorer, right-click the Database node and select Tasks > Find Invalid Objects.
  • Go to the Administration tab of Start Page and select Find Invalid Objects.
  • On the ribbon, select Database > Find Invalid Objects.
  • Press Ctrl + Alt + F.

2. Specify the connection and select a database to search for invalid objects. In the Databases menu, you can:

  • Check All to select all non-system databases available on the server.
  • Uncheck All to clear the checkboxes next to all databases.
  • Refresh the list of databases.
  • Filter a database list by entering the text in the Search bar. The matching text will be highlighted in the list.

Search menu

3. Click Analyze to start searching.

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

Find Invalid Objects manager

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 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 Database Explorer to locate the selected invalid object in Database 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 window.
  • 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.

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

Create a .bat file

dbForge Studio for MySQL 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.

Find invalid objects using the command line

You can search for invalid objects using command line.

1. Click Start, type cmd in the Search programs and files text box, and press ENTER. The Command Prompt opens.

2. Specify the operation you want to perform and the required parameters and arguments (actions you want to run) in the Command Prompt, for example as follows:

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

3. Press ENTER to run the process.

4. Select corresponding command line switches to perform the required operations:

/connection: Specifies the connection string.

/connection:<connection_string>

/password: Specifies the server password and overrides the values specified in the /connection parameter.

/password:<pw>

/database: 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.

/database:<dbname1[,dbname2]>

/alldatabases: Finds invalid objects for all non-system databases specified in the /connection parameter.

/alldatabases

/exitcodes: Displays the list of exit codes that can be returned by the command-line process.

/exitcodes

/log: Generates a log file and saves it to the specified folder.

/log:<filepath>

/report: Generates a report in the CSV file format.

/report:<filename>

/treatwarningaserror: Specifies the behavior for treating warnings. Yes: Treat all warnings as errors, and the subsequent behavior will be determined by the errormode option. No: Do not consider warnings as errors and continue the command line execution.

/treatWarningAsError:[Yes|No]

/errormode: Specifies the application behavior when encountering an error. Ignore: Ignore all errors and continue execution. Abort: Cancel execution if an error occurs.

/errormode:<ignore|abort>
Short keys:    
connection = c
password = pwd
database = db
alldatabases = a
report = r
log = l

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for MySQL.
All Features

All features

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

Request a demo

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