How to find all the tables in MySQL with specific column names in them?

mysql information-schema
Jobi Joy picture Jobi Joy · Oct 11, 2008 · Viewed 530.7k times · Source

I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?

Answer

Ken picture Ken · Oct 11, 2008

To get all tables with columns columnA or ColumnB in the database YourDatabase:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

Related questions

MySQL disable all triggers

For test correctness of query I need disable all triggers in db. I see that in information_schema exists table TRIGGERS. Is possible temporarily disable all triggers using this table? E.g. like: update TRIGGERS set TRIGGERS_SCHEMA='myschema_new' …

Read More
Generate CREATE statements for all MySql Tables

I want All CREATE statements of MySql Tables in 1 query result. For example, INFORMATION_SCHEMA contains all table names,comments etc. but where are the CREATE statements are stored in MySql ? can it be retrieved in one query for all …

Read More
List all tables containing a given column name

How do a I list all tables containing a given column name? I'm using Mysql version 4.1.13-nt-log. I know versions less than 5 dont have an information_scheme DB.

Read More