CODEWITHSUNDEEP
mysqlinformation-schema
Jobi Joy
Jobi Joy

Reputation: 50038

How can I 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 database and list out all tables which have those columns. Is there any easy script?

Upvotes: 1057

Views: 753751

Answers (11)

Radu M.
Radu M.

Reputation: 5748

In older MySQL versions or some MySQL NDB Cluster versions that do not have information_schema, you can dump the table structure and search the column manually.

mysqldump -h$host -u$user -p$pass --compact --no-data --all-databases > some_file.sql

Now search the column name in some_file.sql using your preferred text editor, or use some nifty AWK scripts.

And a simple sed script to find the column. Just replace COLUMN_NAME with yours:

sed -n '/^USE/{h};/^CREATE/{H;x;s/\nCREATE.*\n/\n/;x};/COLUMN_NAME/{x;p};' <some_file.sql
USE `DATABASE_NAME`;
CREATE TABLE `TABLE_NAME` (
  `COLUMN_NAME` varchar(10) NOT NULL,

You can pipe the dump directly in sed, but that's trivial.

Upvotes: 21

love2code
love2code

Reputation: 469

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

Upvotes: 11

Ant&#243;nio Delgado
Ant&#243;nio Delgado

Reputation: 199

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';

Upvotes: 16

Shivendra Prakash Shukla
Shivendra Prakash Shukla

Reputation: 459

If you want to "get all tables only", then use this query:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%'
and TABLE_SCHEMA = 'tresbu_lk'

If you want "to get all tables with columns", then use this query:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%'
AND TABLE_SCHEMA='tresbu_lk'

Upvotes: 12

user2587656
user2587656

Reputation: 419

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)
               ....
        }
    }

Upvotes: 3

Nageen
Nageen

Reputation: 1757

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

Upvotes: 5

GSerg
GSerg

Reputation: 78190

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';

Upvotes: 272

Ken
Ken

Reputation: 78922

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';

Upvotes: 1703

oucil
oucil

Reputation: 4594

For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query...

SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE 
TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT 
TABLE_NAME FROM information_schema.columns WHERE column_name = 
'column_name' AND TABLE_SCHEMA = 'your_db_name');

This came in really handy when we began to slowly implement use of InnoDB's special ai_col column and needed to figure out which of our 200 tables had yet to be upgraded.

Upvotes: 17

Xman Classical
Xman Classical

Reputation: 5436

More simply done in one line of SQL:

SELECT * FROM information_schema.columns WHERE column_name = 'column_name';

Upvotes: 64

baycaysoi
baycaysoi

Reputation: 429

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

Upvotes: 42

Related Questions