How to find all MySQL database tables that have specific column names

MySQL FAQ: How can I find all MySQL database tables that have specific column names?

I found the solution on this SO page. Here’s my take on it.

First, assuming that you want to copy and paste those column names after you get them, I recommend starting the MySQL command line client like this:

$ mysql -sN -u root -p

The -sN options get rid of all of the header and formatting information that MySQL typically puts around query results.

Next, the query you want to use looks like this:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('column1', 'column2')
AND TABLE_SCHEMA='your_database_name';

Example results and output

For example, I was just looking for a column named langcode in all Drupal 8 database tables, so I used this query:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('langcode')
AND TABLE_SCHEMA='drupal8';

This returned a very long list of tables. I’ll show only the first five tables, which are listed like this by the MySQL client:

block_content
block_content__body
block_content_field_data
block_content_field_revision
block_content_revision

As mentioned, the -sN options get rid of all the usual MySQL output formatting, so those are the entire results. If you don’t use those options, your output will look like this instead:

+-----------------------------------+
| TABLE_NAME                        |
+-----------------------------------+
| block_content                     |
| block_content__body               |
| block_content_field_data          |
| block_content_field_revision      |
| block_content_revision            |

Summary

In summary, if you want to know how to find one or more column names that are contained in all of your MySQL database tables, I hope you find this helpful.

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.