CODEWITHSUNDEEP
sqlmysqlsql-order-by
John Scipione
John Scipione

Reputation: 2392

mysql get table column names in alphabetical order

Is it possible to query a MySQL database to get the column names of a table in alphabetical order? I know that 

SHOW COLUMNS `table_name`;

or

DESCRIBE `table_name`;

will give me a list of the columns in a table (along with other info), but is it possible to alter the query in order to get the columns sorted alphabetically. Adding ORDER BY 'Field' didn't work, it gave a syntax error.

Upvotes: 25

Views: 28620

Answers (3)

user3805033
user3805033

Reputation: 157

If you want more details, below query is really handy:

   SELECT COLUMN_NAME  as 'Field',
   COLUMN_TYPE    as 'Type',
   IS_NULLABLE    as 'Null',
   COLUMN_KEY     as 'Key',
   COLUMN_DEFAULT as 'Default',
   EXTRA          as 'Extra'
   from INFORMATION_SCHEMA.COLUMNS
   where TABLE_NAME   = 'my table' and
   TABLE_SCHEMA = 'my database'
   add ordering
   order by Type;  --

Upvotes: 3

Stephen
Stephen

Reputation: 51

Every field was listed twice until I used group by column name

 select c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c 
 where c.TABLE_NAME = `'tbl_name'` 
 group by c.column_name 
 order by c.column_name

Upvotes: 5

OMG Ponies
OMG Ponies

Reputation: 332721

The ANSI INFORMATION_SCHEMA tables (in this case, INFORMATION_SCHEMA.COLUMNS) provide more flexibility in MySQL:

SELECT c.column_name
  FROM INFORMATION_SCHEMA.COLUMNS c
 WHERE c.table_name = 'tbl_name'
-- AND c.table_schema = 'db_name'    
ORDER BY c.column_name

Upvotes: 34

Related Questions