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

Question

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

User · Answer

In version that do not have information schema  older versions  or some ndb s  you can dump the table structure and search the column manually   mysqldump -h host -u user -p pass --compact --no-data --all-databases  gt  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 your s   sed -n    USE  h    CREATE  H x s  nCREATE   n  n  x   COLUMN NAME  x p     lt 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

User · Answer

More simply done in one line of SQL   SELECT   FROM information schema columns WHERE column name    column name

User · Answer

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

User · Answer

SELECT TABLE NAME  COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME LIKE   wild

User · Answer

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

User · Answer

SELECT DISTINCT TABLE NAME  COLUMN NAME   FROM INFORMATION SCHEMA COLUMNS   WHERE column name LIKE  employee     AND TABLE SCHEMA  YourDatabase

User · Answer

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

User · Answer

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 · Answer

SELECT DISTINCT TABLE NAME FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME LIKE   city id   AND TABLE SCHEMA  database

User · Answer

In version that do not have information schema  older versions  or some ndb s  you can dump the table structure and search the column manually   mysqldump -h host -u user -p pass --compact --no-data --all-databases  gt  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 your s   sed -n    USE  h    CREATE  H x s  nCREATE   n  n  x   COLUMN NAME  x p     lt 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

User · Answer

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 · Answer

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 · Answer

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

User · Answer

SELECT TABLE NAME  COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME LIKE   wild

User · Answer

SELECT DISTINCT TABLE NAME  COLUMN NAME   FROM INFORMATION SCHEMA COLUMNS   WHERE column name LIKE  employee     AND TABLE SCHEMA  YourDatabase

User · Answer

More simply done in one line of SQL   SELECT   FROM information schema columns WHERE column name    column name

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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 · Answer

SELECT TABLE NAME  COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME LIKE   wild

User · Answer

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 · Answer

SELECT TABLE NAME  COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME LIKE   wild

User · Answer

SELECT DISTINCT TABLE NAME FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME LIKE   city id   AND TABLE SCHEMA  database

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to information-schema