Getting mysql show create table on more then one table?

Asked By Richard M Dingman 0 points N/A Posted on - 06/16/2013

Hello,

I have to get the output for mysql show create table and write it to one file for each table that I have. How would I do this? Im guessing  stored procedures would help in some way?

• Status: Open
• Question Views: 664
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: Software Others

Answered By Mclean Buono 10 points N/A #173677

Getting mysql show create table on more then one table?

Hello Richard,

As I understand you want to write the output of each create table statement in a separate file.

There are several ways of doing it –

1. You can use a stored procedure or any programming language like PHP, Java and execute some commands.
   1. SHOW TABLES IN <YOUR-DATABASE-NAME>.
   2. Loop on the output of above command, extract table name in each iteration and execute SHOW CREATE TABLE <TABLE-NAME>, write each statement in a separate file.
2. Execute from command line mysqldump command (For each dumped table, a tbl_name.sql file be created.)
   1. mysqldump -u [username] -p -T[/path/to/directory] [database] –no-create-db –no-data
   2. Replace [username] with the MySQL username
   3. [/path/to/directory] with the path to the directory where data will exported to.
   4. [database] the name of the database from which all tables will be exported.

I would suggest trying second option as its quicker and easier.

Regards,

Mclean

About Mclean Buono

Questions
6

Answers
99

Best Answers
33

• Vote Up 0 Vote Down

• Posted on - 06/26/2013
• Question Category: Software Others