Is there a way to search the database if a column name / field name exists in a table in mysql?
1
-
3possible duplicate of How to find all the tables in MySQL with specific column names in them?dayuloli– dayuloli2014-05-02 03:43:10 +00:00Commented May 2, 2014 at 3:43
Add a comment
|
4 Answers
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' );
Comments
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'
Comments
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.
2 Comments
DhruvPathak
That would just search 1 table instead of whole database.
Stefan
hmm ok - well i did understand the question if a certain column is in a certain table!
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';
