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

Joy_8091 0
Viewed 635644

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

information-schema mysql
11 Answers

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';
Ken_200740 
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';
user_116830 
SELECT DISTINCT TABLE_NAME, COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE column_name LIKE 'employee%'  
AND TABLE_SCHEMA='YourDatabase'
user_5386210

Use this one line query. Replace desired_column_name by your column name.

SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name';
user_86347310 
select distinct table_name 
from information_schema.columns 
where column_name in ('ColumnA') 
    and table_schema='YourDatabase';
    and table_name in 
    (
        select distinct table_name 
        from information_schema.columns 
        where column_name in ('ColumnB')
              and table_schema='YourDatabase';
    );

That ^^ will get the tables with ColumnA and ColumnB instead of ColumnA or ColumnB like the accepted answer

user_4267590 
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%city_id%' AND TABLE_SCHEMA='database'
user_39966240

The problem with information_schema is that it can be terribly slow. It is faster to use the SHOW commands.

After you select the database you first send the query SHOW TABLES. And then you do SHOW COLUMNS for each of the tables.

In PHP that would look something like


    $res = mysqli_query("SHOW TABLES");
    while($row = mysqli_fetch_array($res))
    {   $rs2 = mysqli_query("SHOW COLUMNS FROM ".$row[0]);
        while($rw2 = mysqli_fetch_array($rs2))
        {   if($rw2[0] == $target)
               ....
        }
    }
user_25876560
Related