List all tables containing a given column name

mysql information-schema
smokinguns picture smokinguns · Feb 8, 2010 · Viewed 20.3k times · Source

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.

Answer

Ed Massey picture Ed Massey · May 27, 2012

Find all tables and columns where column names are like the search term:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%partial_column_name%'
    AND TABLE_SCHEMA='YourDatabase';

Related questions

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

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?

Read More
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