Mysql – How to print all fields for all tables in thesql database

databasemysql

I'm trying to learn the structure of a mysql database I'm not familiar with. It's been years since I used mysql.

I'm looking for a way to print all the fields for all the tables in this database. At the moment, I'm using

Show Fields from <insert table name>

However, this slow and clunky. Is there a faster way?

Best Solution

To see all tables of a specific database (like mydb), do this:

USE mydb
SHOW TABLES;

To see all fields, indexes, storage engine, table options, partition layout in mydb.mytable, do this:

USE mydb
SHOW CREATE TABLE tblname\G

To see all tables in all databases in bulk, here is a script:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--routines --triggers --no-data --all-databases"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLSchema.sql
less MySQLSchema.sql

If you want to see a specific database (like mydb), do this:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL} -p${MYSQL_PASS}"
DBTOSHOW=mydb
MYSQLDUMP_OPTIONS="--routines --triggers --no-data --databases ${DBTOSHOW}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLSchema.sql
less MySQLSchema.sql

This should be the quickest way because accessing the information_schema database can be somewhat slow if there are a lot of busy InnoDB tables.

Give it a Try !!!