Shulou(Shulou.com)06/01 Report--

If you've ever tried to locate specific columns in a large database, I'm sure you'll agree that this can be a daunting task. You can gather a lot of information about the structure of DB from the information_schema schema. It has a list of all tables and all fields in the table. You can then use the information obtained from the table to run the query. The specific tables involved are SCHEMATA, TABLES, and COLUMNS. There are some foreign keys that allow you to build exactly how to create tables in the schema.

However, an easier way to perform a database-wide search is to use Navicat Premium. Navicat is available in Non-Essentials Edition and provides the find in Database / Schema feature for finding data in tables / views or object structures in databases and / or schemas. In today's article, we will learn how to use it.

Navicat Premium is a database development tool that allows you to connect to both MySQL,MariaDB,MongoDB,SQL Server,Oracle,PostgreSQL and SQLite databases from a single application. Compatible with cloud databases such as Amazon RDS,Amazon Aurora,Amazon Redshift,Microsoft Azure,Oracle Cloud,Google Cloud and MongoDB Atlas. You can quickly and easily build, manage, and maintain databases.

Positioning column

We first look for a column in the database. We want to find the "release_year" column in the Sakila sample database. This is how we deal with it:

Open the find window in the database / schema; select tools from the menu bar-> find in the database / schema.

Select the target connection, database, or schema.

Enter a search string in the find what text box.

Select the structure item in the look in drop-down list. Another option, of course, is "data".

Select search Mode. Options include include, full word, prefix, or regular expression.

Check the case-insensitive box to disable case-sensitive search.

Because we selected structure in the look in drop-down list, we can now choose to search for different objects, including tables, views, functions, queries, or events.

After filling in or selecting all fields, the form should look like this:

Now, continue and click the find button to get the results. In this case, Navicat matches the "release_year" column in a table:

You can double-click an object in the find results list to view the record or structure. It highlights:

Search data

It is hardly worthwhile to try to find a given value in the entire database without a search tool. In Navicat, all we need to do is the same process as above, except for now we will select data from the find drop-down menu.

This is the result of the find what value of the prefix JOHN from the search Mode drop-down list:

As you can see, this more general search leads to more matches.

Double-click the object in the find results list again, and the record is displayed in the new tab:

Pay attention to the query generated by Navicat to get the desired results.

Conclusion

Navicat's find in Database / Schema tool greatly facilitates finding data or object structures in the entire database or schema. Compared with alternatives to the information_schema model, there is virtually no competition.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.