MySQL: sort order "SHOW TABLES"

Question

How are the tables ordered returned by "SHOW TABLES"?

For example the output for the information_schema database looks like this:

CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENGINES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
INNODB_CMP_RESET
INNODB_TRX
INNODB_CMPMEM_RESET
INNODB_LOCK_WAITS
INNODB_CMPMEM
INNODB_CMP
INNODB_LOCKS

Answer 1

The below code works pretty well for me to list specific tables on any of my database and sort them by their name as asked.

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_here' AND TABLE_NAME LIKE '%letters_from_your_table_here%' ORDER BY TABLE_NAME DESC

Answer 2

See Sergei Golubchik's answer from SHOW DATABASES does not order infomation_schema correct: "no SHOW command sorts the result".

If you need the tables names sorted you can query information_schema.tables, something like:

select table_name from information_schema.tables 
 where table_schema = 'your_db_name' order by table_name;

Answer 3

Try this:

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'information_schema' ORDER BY TABLE_NAME