Listing tables in mysql database

Question

Hello

How do you list the names / fields of all the tables in a mysql database ?

Any code examples would be helpful,

Thanks!

Answer 1

SHOW_COLUMNS - http://dev.mysql.com/doc/refman/5.1/en/show-columns.html

Answer 2

You can use thsi query for all columns in your database and their corresponding table name-

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 

This will give list of all tables -

SHOW TABLES

For column names available in specific table, you can use any of below -

DESCRIBE table_name 
SHOW FIELDS FROM table_name 
SHOW COLUMNS FROM table_name 

Answer 3

To get list of all tables in a database the syntax is -

SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions.

Reference - http://dev.mysql.com/doc/refman/5.1/en/show-tables.html

If you are using PHP as a server side language then it can be done as follows -

http://www.java2s.com/Code/Php/MySQL-Database/Getalltablesinadatabase.htm