How do I search a MySQL database for a specific column name?

Question

I'm trying to find if my database has a column named sort_method. I have had a look around the forums, but I don't seem to be able to find the right answer. My database has over 300 tables, so manually looking for it is not an option.

I'm using SQL queries from phpMyAdmin.

Dublicate of http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them — kapandron, Aug 17 '12 at 17:02

score 77 · Accepted Answer · answered Aug 17 '12 at 17:07

77

SELECT table_name,table_schema
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name='sort_method'

answered Aug 17 '12 at 17:07

Andy

1,618
11
13

score 8 · Answer 2 · edited Aug 06 '21 at 16:07

8

You can query the INFORMATION_SCHEMA.COLUMNS system table:

SELECT COLUMN_NAME, TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE COLUMN_NAME = 'sort_method'

More information is in http://dev.mysql.com/doc/refman/5.0/en/columns-table.html.

edited Aug 06 '21 at 16:07

Peter Mortensen

30,738
21
105
131

answered Aug 17 '12 at 16:59

Mike Christensen

88,082
50
208
326

The link is (effectively) broken: *"Page Not Found"* – Peter Mortensen Aug 06 '21 at 16:08

score 8 · Answer 3 · answered Aug 17 '12 at 17:01

8

Try

SELECT DISTINCT TABLE_NAME 
     FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'columnName'
        AND TABLE_SCHEMA='YourDatabase';

answered Aug 17 '12 at 17:01

John Woo

258,903
69
498
492

How do I search a MySQL database for a specific column name?

3 Answers3

Linked