Return order of MySQL SHOW COLUMNS?

To return order of MySQL SHOW COLUMNS, you need to use ORDER BY clause. The syntax is as follows −

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘yourTableName’
AND column_name LIKE 'yourStartColumnName%'
ORDER BY column_name DESC;

Let us create a table in database TEST. The query to create a table is as follows −

mysql> create table OrderByColumnName
   -> (
   -> StudentId int,
   -> StudentFirstName varchar(10),
   -> StudentLastName varchar(10),
   -> StudentAddress varchar(20), 
   -> StudentAge int,
   -> StudentMarks int
   -> );
Query OK, 0 rows affected (1.81 sec)

Case 1 −

In this, the result is in descending order. Here is the query to return order of show column in MySQL −

mysql> SELECT COLUMN_NAME
   -> FROM INFORMATION_SCHEMA.COLUMNS
   -> WHERE table_name = 'OrderByColumnName'
   -> AND column_name LIKE 'student%'
   -> ORDER BY column_name DESC;

The following is the output −

+------------------+
| COLUMN_NAME      |
+------------------+
| StudentMarks     |
| StudentLastName  |
| StudentId        |
| StudentFirstName |
| StudentAge       |
| StudentAddress   |
+------------------+
6 rows in set (0.00 sec)

Case 2 − If you want the result in ascending order, there is no need to write ASC keyword because by default result will be in ascending order.

The query is as follows −

mysql> SELECT COLUMN_NAME
   -> FROM INFORMATION_SCHEMA.COLUMNS
   -> WHERE table_name = 'OrderByColumnName' 
   -> AND column_name LIKE 'student%'
   -> ORDER BY column_name;

The following is the output −

+------------------+
| COLUMN_NAME      |
+------------------+
| StudentAddress   |
| StudentAge       |
| StudentFirstName |
| StudentId        |
| StudentLastName  |
| StudentMarks     |
+------------------+
6 rows in set (0.00 sec)