What is Mysql select statement to show a list of tables?

Question

I want to write a program that can show the user a list of tables in the database and also show the descriptions of those tables. So can I do a "select * from system_table" or something like that?

Answer 1

This will give you a list of tables:

show tables;

To describe each table:

describe table_name;

To get both at the same time try:

SELECT * FROM DOMAIN.TABLES WHERE TYPE = 'TABLE'

SELECT * FROM DOMAIN.COLUMNS WHERE TABLETYPE = 'TABLE'

The results are similar to MySql show and describe statements

Answer 2

In addition to show tables, MySQL 5.0+ also supports the INFORMATION_SCHEMA meta-database:

SELECT table_name, table_comment FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name';

information_schema.tables also has other information in it, if you're curious.

Note that if you didn't provide a comment when you created the table and use InnoDB, it will fill the table_comment column with unnecessary data, such as the InnoDB space reserved for this table or the foreign key constraints.