Identify column name information from table schema

Question

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?

score 0 · Answer 1 · answered Apr 01 '21 at 20:55

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'

Identify column name information from table schema

1 Answers1