MySQL Get All Tables Name and Column Information

Many time we need all tables information like table names, columns in table in MySql database. MySql or most of the RDS keep these information in some table only. Using SQL queries we can get all this information.
MySql stores these information in system table information_schema.tables . By executing queries on this table you can get all required information.

select * from information_schema.tables

get-all-table-mysql

information_schema.tables has following columns which one can use to get all required information

  1. TABLE_SCHEMA:

    This column store name of db schema or in general database name.

  2. TBALE_NAME

    This column stores table name along with db schema name in that row.

  3. TABLE_TYPE

    This column contains table type name like system view, view or base table.

Other columns many be useful for purposes.

By using information_schema.tables table and you can get all table name and their types and you can use this table to get tables column information using joins.

get-all-table-mysql-2