If you've ever tried to locate a specific column in a large database, I'm sure that you'd agree that it can be a painstaking task. You can glean a lot of information about the DB structure from the information_schema schema. It has a list of all tables and all fields that are in a table. You can then run queries using the information that you have gotten from this table. The specific tables involved are SCHEMATA, TABLES and COLUMNS. There are foreign keys such that you can build up exactly how the tables are created in a schema.

However, an easier way to perform a database-wide search is to use Navicat Premium. Available in Non-Essentials Edition, Navicat provides a Find in Database/Schema feature for finding data within tables/views or object structures within a database and/or schema. In today's blog, we'll learn how to use it.

Locating a Column

Let's start by finding a column within our database. We'd like to find the "release_year" column within the Sakila Sample Database. Here's how we would go about it:

• Open the Find in Database/Schema window; choose Tools -> Find in Database/Schema from the menu bar.
• Select a target Connection, Database and/or Schema.
• Enter the search string in the "Find what" text box.
• Choose the "Structure" item in the "Look in" drop-down list. The other option is of course "Data".
• Choose the "Search Mode". Choices include Contains, Whole Word, Prefix or Regular Expression.
• Check the "Case Insensitive" box to disable case sensitive search.
• Since we selected "Structure" in the "Look in" drop-down list, we can now choose to search different objects, including Tables, Views, Functions, Queries, and/or Events.
  
  Here is what the form should look like with all of the fields filled in and/or selected:
  
  
  
• Now, go ahead and click the Find button to obtain the results. In this case Navicat matched the "release_year" column in one table:
  
  
  
  You can double-click an object in the Find Results list to view the record or the structure. It'll be highlighted:
  
  

Searching for Data

Trying to find a given value within the entire database without a search tool is scarcely worth the trouble. In Navicat, all we need to do is follow the same process as above, except that now we'll select "Data" from the "Look in" drop-down.

Here's the results for a "Find what" value of "JOHN" with "Prefix" selected from the "Search Mode" drop-down:

As you can see, this more general search resulted in more matches.

Again, double-clicking an object in the Find Results list displays the record(s) in a new tab:

Notice the query that Navicat generated to fetch the desired results.

Conclusion

Navicat's Find in Database/Schema tool greatly facilitates finding data or object structures within an entire database or schema. Compared with the alternative of information_schema schema, there is really no contest. You can learn more about Navicat Premium's features on the product page.