Search code examples
mysqlsqlselectinformation-schema

Error when selecting colum names from table in MySQL information schema

I am trying to get a list of the column names from a specific table in MySQL. I am running:

SELECT column_name
FROM information_schema.columns
WHERE table_name = `test 2.2`
    AND table_schema = test

The database is called test and the table name is test 2.2 and the rest of the syntax looks correct. However I keep receiving the error

Error Code: 1054. Unknown column 'test 2.2' in 'where clause'

Is there another way that I can do what I want and/or how do I get around this error?

Solution

  • Object names (in this case: a table name) are stored in the information schema as string literals, so they should be queried with single quotes ('):

    SELECT column_name
FROM information_schema.columns
WHERE table_name = 'test 2.2' AND table_schema = 'test'
-- Here -----------^--------^--------------------^----^