Getting mysql show create table on more then one table?

Asked By 0 points N/A Posted on -
qa-featured

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?

SHARE
Answered By 10 points N/A #173677

Getting mysql show create table on more then one table?

qa-featured

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

Related Questions