0

I have multiple tables with with varying names for the same piece of data. For example, day vs business_day. I'd like to identify what column names exist for which table. I think this can be done using schema information, but I'm not familiar with it.

For simplicity, the tables below are 3 separate tables with these column names.

table_1

day    city  weather

table_2

business_day location  status

table_3

day   city  rain

Where day and business_day and city and location are the same piece of information, but unfortunately different naming conventions so I wouldn't be able to use the same selection criteria in a WHERE clause. I'd like to search the table information to see which tables have day or business_day and city or location. The other columns are not important to me in this example. How can I identify table information in this way?

Improve this question
1
  • MySQL or Postgres? Please remove the irrelevant tag (unless you want solutions for both). Also, you seem to know that information_schema has something to do with the answer; did you try anything?
    – mustaccio
    Apr 1, 2021 at 20:49

1 Answer 1

Reset to default
0

In MySQL and Postgres you can use

MySQL INFORMATION_SCHEMA.COLUMNS Postgres INFORMATION_SCHEMA.COLUMNS

SELECT COLUMN_NAME, table_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = 'db_name'
  AND column_name LIKE 'day' OR column_name LIKE 'business_day'
Improve this answer
5
  • column_name LIKE 'day' -- really? Please don't make me downvote your answer.
    – mustaccio
    Apr 1, 2021 at 21:00
  • what do you mean, he asked for a solution and day is a string so if he has such a cplumn, so be it neither mysql nor postgres will thrw an error
    – nbk
    Apr 1, 2021 at 21:03
  • In the absence of wildcard symbols column_name LIKE 'day' is equivalent to column_name = 'day', so why did you choose the LIKE predicate?
    – mustaccio
    Apr 1, 2021 at 22:56
  • Because i use in most cases LIKE with stirngs and rarely =
    – nbk
    Apr 1, 2021 at 23:00
  • 1
    That's a good reason to downvote then, thank you.
    – mustaccio
    Apr 2, 2021 at 1:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.