How to get database structure in MySQL via query

I think that what you're after is DESCRIBE

DESCRIBE table;

You can also use SHOW TABLES

SHOW TABLES;

to get a list of the tables in your database.

To get the whole database structure as a set of CREATE TABLE statements, use mysqldump:

mysqldump database_name --compact --no-data

For single tables, add the table name after db name in mysqldump. You get the same results with SQL and SHOW CREATE TABLE:

SHOW CREATE TABLE table;

Or DESCRIBE if you prefer a column listing:

DESCRIBE table;

Take a look at the INFORMATION_SCHEMA.TABLES table. It contains metadata about all your tables.

Example:

SELECT * FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE TABLE_NAME LIKE 'table1'

The advantage of this over other methods is that you can easily use queries like the one above as subqueries in your other queries.

using this:

SHOW CREATE TABLE `users`;

will give you the DDL for that table

DESCRIBE `users`

will list the columns in that table

That's the SHOW CREATE TABLE query. You can query the SCHEMA TABLES, too.

SHOW CREATE TABLE YourTableName;

You can pick any one of the command below. All of them are more or less same:

SHOW CREATE TABLE TABLE_NAME;

DESC TABLE_NAME;

SHOW FULL COLUMNS FROM TABLE_NAME; (for column properties)

EXPLAIN TABLE_NAME;

DESCRIBE TABLE_NAME;

In the following example,

playground is the database name and equipment is the table name

Another way is using SHOW-COLUMNS:5.5 (available also for 5.5>)

$ mysql -uroot -p<password> -h<host> -P<port> -e \
    "SHOW COLUMNS FROM playground.equipment"

And the output:

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| type  | varchar(50) | YES  |     | NULL    |                |
| quant | int(11)     | YES  |     | NULL    |                |
| color | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

One can also use mysqlshow-client (also available for 5.5>) like following:

$ mysqlshow -uroot -p<password> -h<host> -P<port> \
    playground equipment

And the output:

mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: playground  Table: equipment
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type        | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)     |                   | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| type  | varchar(50) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
| quant | int(11)     |                   | YES  |     |         |                | select,insert,update,references |         |
| color | varchar(25) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+