Lowercase column names when using SELECT

Question

I use the Kohana structure and I need to convert the column names to lowercase. I do not control the structure of the db table. I want to do the following: SELECT LOWER(*) FROM .....

but MYSQL doesn't like it. What is the correct way to output lowercase column names if I don't know what the column names will be?

Answer 1

Found here http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

 SELECT LOWER(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Table' 

Below you can see both the MSSQL and MySQL syntax for creating a dynamic query using the results of a column from the above query.

MSSQL Syntax

---

 DECLARE @ColumnNames [nvarchar](1024) SELECT @ColumnNames = COALESCE(@ColumnNames + ', ', '') + LOWER(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table' DECLARE @Sql [nvarchar](1024) = 'SELECT ' + @ColumnNames + ' FROM Table ' --Remember to put spaces after SELECT and before FROM EXEC(@Sql) 

With this, you dynamically create your request and then execute it.

MySQL syntax

---

 SELECT @ColumnNames := GROUP_CONCAT(LOWER(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table'; SET @Sql = CONCAT('SELECT ', @ColumnNames, ' FROM Table '); PREPARE stmt1 FROM @Sql; EXECUTE stmt1;