19

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

rjmcb
  • 3,437
  • 7
  • 30
  • 45
  • 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
  • 40,405
  • 15
  • 109
  • 170
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,177
  • 2
  • 33
  • 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,092
  • 9
  • 46
  • 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
  • 1,527
  • 5
  • 22
  • 39