Menu
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
552
MySQL SHOW TABLES command example To use the SHOW TABLES command, you need to log on to the MySQL server first. On opening the MySQL Command Line Client, enter your password. Select the specific database. Run the SHOW TABLES command to see all the tables in the database that has been selected.
In MySQL, locked tables are identified using the SHOW OPEN TABLES command. In its simplest form is displays all locked tables. All open tables in the table cache are listed, but the IN_USE column indicates of the table is locked. When the first lock is taken, the value increments to 1.
The SHOW OPEN TABLES statement displays the lists the non-TEMPORARY tables which are currently open in the table cache. In addition to names of the tables this statement also provides the name of the database the table is in and binary values (0 or 1) specifying whether the table is in use and if it is locked.
The easiest way to find all tables in SQL is to query the INFORMATION_SCHEMA views. You do this by specifying the information schema, then the “tables” view. Here's an example. SELECT table_name, table_schema, table_type FROM information_schema.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
