Get Create Table Query for all Tables in MySQL

 In a MySQL database whenever we need to generate the create table query for any existing table. We can use the below query.

show create table test_table;

If you need to generate create table query for all the tables then the above query will be cumbersome because you have to run it for every table.

We can use mysqldump to generate create table query for all the tables in one go.

Open your favorite terminal and execute the below command to check if you have the mysqldump on your system. If you don't have it, please install it first. Usually, it comes with mysql-client software but check the web for more help.

mysqldump --version

Now you have mysqldump ready, We can use the below command to generate the create table query for all the tables.

mysqldump -h ${HOST} -u ${USER} -p -d --compact --column-statistics=0 ${DB_NAME}|egrep -v "(^SET|^/\*\!)" > tables.sql

${} represents the variable you can replace with your DB config. We have used -p So it will prompt for the password which you have to enter to proceed.

Sample command: 

mysqldump -h localhost -u root -p -d --compact --column-statistics=0 test_db|egrep -v "(^SET|^/\*\!)" > tables.sql