CODEWITHSUNDEEP
mysqlsqldatabase
rjmcb
rjmcb

Reputation: 3745

Search database if column name/field name exists in a table in mySQL

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

Upvotes: 20

Views: 28276

Answers (4)

Anand Rajagopal
Anand Rajagopal

Reputation: 1633

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

Upvotes: 1

Stefan
Stefan

Reputation: 2068

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.

Upvotes: 6

vikiiii
vikiiii

Reputation: 9476

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'

Upvotes: 6

DhruvPathak
DhruvPathak

Reputation: 43245

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

Upvotes: 21

Related Questions