Select ONLY field names from table WITHOUT information_schema

Question

I can execute the queries:

SELECT COLUMNS FROM _table-name_ ...or... SELECT COLUMNS FROM _table-name_

Which will give the example result of: +-------+--------+------+-----+---------+ | Field | Type | Null | Key | Default | +-------+--------+------+-----+---------+ | id | char | no | pri | (NULL) | + + + + + + | name | char | no | | (NULL) | +-------+--------+------+-----+---------+

This is OK but I only want the Field column. I have seen solutions where you can query information_schema but, unfortunately, I do not have that option to me so I am looking for a solution along the lines of

SELECT Field FROM (SHOW COLUMNS FROM _table-name_) (but I know this is not possible)

Also, this needs to be possible in one query.

Answer 1

The solution provided by MySQL is to use information_schema. You need to explain why this is not an option for you, and what you're trying to achieve, if anyone can suggest an alternative.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME='mytable'

You can query the I_S tables using a qualified tablename (as I show above) even if your default schema is currently some other schema.

You always have privileges to read I_S. If you have privileges to read the tables in your specified schema, you can read the corresponding rows of metadata from I_S tables.