MySQL: how to get the list of fields in a table using ODBC

Question

I am connecting to a MySQL DB trough a terminal who only have a program with an ODBC connection to a MySQL DB. I can put querys in the program, but not access MySQL directly.

I there a way to query the DB to obtain the list of fields in a table other than

select * from table

??

(don't know why but the select returns a error)

Answer 1

describe *tablename*

Answer 2

SELECT
  COLUMN_NAME
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME       = 'MyTable'
  AND TABLE_SCHEMA = 'SchemaName'  /* added upon Bill Karwin's comment (thanks) */

More info on INFORMATION_SCHEMA is in the docs.

Answer 3

This works on most databases:

select * from table where 1=0

You get no data in the result set but you do get the column metadata.

Answer 4

This:

SHOW COLUMNS FROM Tablename

lists the fields in a table and their properties (data type, whether null values are allowed, whether the field is a primary key, the default value if one has been set, etc.)