Navicat Blog

December 6, 2017 by Robert Gravelle

Whether your database of choice is app like MySQL, MariaDB, SQL Server, Oracle, and PostgreSQL, or a cloud-based service such as Amazon RDS, Amazon Aurora, Amazon Redshift, SQL Azure, Oracle Cloud and Google Cloud, you'll inevitably be looking for a piece of data whose location eludes you. For those occasions, you'll be happy that you use one of Navicat's award winning database administration products.

Available in all editions, with the except of Navicat Essentials, the Find In Database/Schema tool allows you to search tables, views and even object structures within a database and/or schema.

You'll find it under the Tools item in the main menu:

Searching for Data

Suppose that we were looking for a record associated with the word “jungle”. You could bushwhack your way through each and every table or simply enter the search term in the Find in Database screen. There are four Search Modes to choose from: Contains, Whole Word, Prefix and even using powerful Regular Expression pattern matching.

  • Contains will match your search term against any part of a text value.
  • Whole Word will only match if the text value is exactly the same as the search term.
  • Prefix matches the start of a text value.
  • Regular Expression apply pattern matching to text values.

Matching is performed on a case insensitive basis unless you uncheck the Case Insensitive box.

The results of your search are displayed in the Find results pane. The table/view Name is displayed, along with the Number of Matched Records. I got two matches for “jungle”:

To take a better look at the matched rows, just double-click the item in the Find results pane. That will open a new Query Editor with only the row that contains the match:

Searching for Object Structures

An Object Structure search looks for matches against database object names. These would include Tables, Views, Functions, Queries, Indexes, Triggers, Events and/or Materialized Views.

The Search Modes include the same four as in data searches and can either be case sensitive or insensitive.

For this search I set the Search Mode to “Prefix” so that the Find In Database/Schema tool would find object names that begin with my search term. Not surprisingly, in a movie rental store database, a Prefix of “film_” hit a few times!

Below are the results. Notice that the object type and match are both included in the Find results pane. The search term text within Matched Content is highlighted in red as well:

This time, double-clicking an item in the Find results pane opens the appropriate editor for that database object. For example, clicking the last match in the list for the inventory table opens the Table Editor with the matching field selected and highlighted:

Conclusion

The Find In Database/Schema feature makes searching for text content within data and object structure names so much easier than the alternative that you'll ask yourself how you every got by without it. For more information on how to use the tool, there's a video about that very subject on YouTube.

Navicat Blogs
Feed Entries
Blog Archives
Share