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.

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.