How to generate DDL for all tables in a database in MySQL

Question

How to generate the DDL for all tables in a database of MySQL at once. I know that the following query will output the DDL for a table. But I want DDL of all tables at once because I am having hundreds of tables in my database.

show create table <database name>.<table name>;

For example:

show create table projectdb.customer_details; 

The above query will result in DDL of customer_details table.

I am using MySQL with MySQL workbench on Windows OS.

Solution

You can do it using the mysqldump command line utility:

mysqldump -d -u <username> -p -h <hostname> <dbname>

The -d option means "without data".

Comments

[Pargat]

I am getting error. Here's my command : mysqldump -d -u root -p pps -h 127.0.0.1 shopping_store;

[Swapnil]

What is the error you're getting? It could probably be because of the space between "-p" and your password.

[Ben]

That function is deprecated as of PHP 5.5. They recommend using SHOW TABLES FROM sometable instead (I think they meant somedb though).

[tftdias]

Upvoted. There is just a syntax error in your answer. The option -p does not accept an argument so, if you try to write your password after this option flag, mysqldump will interpret it as the database name. Just use it without <password>, it will be requested after you execute the command.

[AJ.]

Interesting, this may have changed (the answer was written in 2011). You used to be able to put the password on the command line.

[sancheese]

for password use option: --password="<pass>"

[Zane]

mysqldump Ver 10.16 Distrib 10.1.23-MariaDB, for Linux (x86_64) works with the syntax of the answer.

[Akshay Lokur]

Corrected Syntax Error, command can be like this: mysqldump -d -u root -p -h 10.216.12.11 My_Schema > ./MySchema_DB_DDLs_Dump.sql

[wkrueger]

password is asked afterwards bc its unsafe to type passwords in prompts (it can be logged in history etc)