20

Is there a way to search the database if a column name / field name exists in a table in mysql?

rjmcb
  • 3,595
  • 9
  • 32
  • 46
  • 3
    possible duplicate of [How to find all the tables in MySQL with specific column names in them?](http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them) – dayuloli May 02 '14 at 03:43

4 Answers4

21

use INFORMATION_SCHEMA database and its tables.

eg :

    SELECT *
FROM   information_schema.columns
WHERE  table_schema = 'MY_DATABASE'
       AND column_name IN ( 'MY_COLUMN_NAME' );
DhruvPathak
  • 42,059
  • 16
  • 116
  • 175
6
SHOW COLUMNS FROM tablename LIKE 'columnname'

have fun ! :-)

UPDATE:

As mentioned in the comments, this searches only one table, not the whole database (every table). In that case, please refer to DhruvPathak's answer.

Stefan
  • 2,028
  • 2
  • 36
  • 53
6

If you want to search in the whole database then you should try 

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND COLUMN_NAME = 'column_name'

And if you want to search in the particular table then you should try 

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'table_name' 
AND COLUMN_NAME = 'column_name'
vikiiii
  • 9,246
  • 9
  • 49
  • 68
1

If you want search two or more columns use following below metioned.

 SELECT DISTINCT TABLE_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME IN ('columnA','ColumnB')
            AND TABLE_SCHEMA='YourDatabase';
Anand Rajagopal
  • 1,593
  • 6
  • 24
  • 40