How to get database structure in MySQL via query

Question

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

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='products'; 

where Table_schema is database name

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

SHOW CREATE TABLE YourTableName;

A variation of the first answer that I found useful

Open your command prompt and enter (you dont have to be logged into your mysql server)

mysqldump -hlocalhost -u<root> -p<password>  <dbname>  --compact --no-data > </path_to_mydump/>mysql.dmp