select query to find the particular column in the database

shijoe · #1 (**permalink**) 04-27-09, 03:59

I have yearmonth column in more than 50 tables in the customers database. I have to find the what are all the tables are having yearmonth column from the customers data base.
Please provide the select query.Thanks

mike_bike_kite · #2 (**permalink**) 04-27-09, 05:37

I think this should work:

Code:

select  t.table_name
from    information_schema.COLUMNS c,
        information_schema.TABLES t
where   c.table_schema = "customers"
        and t.table_schema = "customers"
        and t.table_type = "BASE TABLE"
        and c.table_name = t.table_name
        and c.column_name = "yearmonth";

Mike

Internet Services

Web Development

Digital Marketing

Consumer Tech