2

I have a huge database and a column name that I am not sure if it exists in any of my tables. How can I check its existance?

VladH
  • 383
  • 4
  • 16
  • VladH indicated below on my answer that this is what he was looking for - if the question could be reworded to reflect this, I feel it would be less broad. He even specified that his issue was not knowing which table exactly held the column. – SubSevn Jun 27 '13 at 13:58
  • I edited your post in order to keep everything clear. – VladH Jun 28 '13 at 10:56

5 Answers5

3

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

VladH
  • 383
  • 4
  • 16
SubSevn
  • 1,008
  • 2
  • 10
  • 27
  • From http://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql – SubSevn Jun 26 '13 at 13:18
  • cheers dude! the problem was that I didn't know the name of the containing table but apparently " SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'db_name' AND COLUMN_NAME = 'column_name' " worked. – VladH Jun 26 '13 at 13:23
  • Yeah, you can edit the `WHERE` clause to suit your needs - glad it worked out for you! – SubSevn Jun 26 '13 at 13:25
2

If you need to check all tables, you may have to list the tables first:

SHOW TABLES FROM database_name;

Then, loop through the tables (in code, e.g., PHP), and execute this query:

SHOW COLUMNS FROM database_name.table_name LIKE 'mycol';

This will return a result if the column exists on the table.

You can also select directly from the information_schema, but I have found this to be very slow in some cases with MySQL and large databases.

Seth
  • 1,353
  • 1
  • 8
  • 17
1
SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

please check above code with your database
Sameer Rangrez
  • 162
  • 5
0

SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME ='table_name' AND COLUMN_NAME = 'col_name'
Ahmed
  • 452
  • 3
  • 7
0

Use the information schema 

select *
from information_schema.tables
where table_name = 'employee'
and table_schema = 'test';
Bhavik Shah
  • 5,125
  • 3
  • 23
  • 40