How to check if Column name exists in database. In Mysql

Question

I have a list of column names, that may or may not be in a specific MySQL database

Is it possible to query the DB to see if a column name exists? When i do not know which table it might be in?

If yes, how would I do so?

This question has an answer that assumes i know which table it is in. I do not MySQL: How to create Column if not exists?

this answer is close, but also assumes i know exactly where each column is (i am trying to automate insertion, and the column names that exist may be different from the column names i will insert in the future, but contain the same data) How to select specific rows if a column exists or all rows if a column doesn't

Does this answer your question? How to select specific rows if a column exists or all rows if a column doesn't — mustaccio, Nov 12 '21 at 13:23
Maybe. is there a way to get only column names from the information schema? — yoni chanowitz, Nov 12 '21 at 13:59
Yes you can, but why don't you know what the table structures are? To me, this sounds like trying to Reverse Engineer someone else's database. Questionable at best. — Phill W., Nov 12 '21 at 14:15
i do, there are just so many of them, and they arent organized according to the way my client wants to insert data. i'm trying to get data from a csv file and send it to the database. some of the headers of the spreadsheet are existing column names in the db, and some are not. i am trying to automate inserting data to the ones that exist, without having to look into every table and do it manually — yoni chanowitz, Nov 12 '21 at 14:27

score 0 · Answer 1 · answered Nov 12 '21 at 14:44

0

thanks to mustaccio for pointing out the other question that explains the Information_schema.

with MySql you would query SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS; and get a list of all columns

i would add that it gives a list of ALL column names, even the ones generated by MySql

answered Nov 12 '21 at 14:44

yoni chanowitz

1
3

And if you add ...where column_name in ('COLUMNS','IWANT','TOFIND') it will return just the columns you are interested in, won't it? – mustaccio Nov 12 '21 at 16:26
i will try that – yoni chanowitz Nov 12 '21 at 16:37

score 0 · Answer 2 · edited Nov 12 '21 at 23:08

0

As you noted you can use the Information_Schema, and then know the tables the columns are part of as well:

SELECT *
    FROM   information_schema.columns
    LEFT JOIN information_schema.tables
          ON columns.table_name = tables.table_name 
         AND columns.table_catalog = tables.table_catalog`

SQL Fiddle Link:

http://sqlfiddle.com/#!9/9eecb/254846

edited Nov 12 '21 at 23:08

Rick James

78,038
5
47
113

answered Nov 12 '21 at 15:21

exfallsburg1

1

How to check if Column name exists in database. In Mysql

2 Answers2