How to generate a database script

for MySQL Workbench, you can simply use its Migration wizard in which it has an option to export the database and data out.

Depending on exactly how you want to do things:  Look at mysqldump:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

You can export the create statement via PHPMyAdmin.

When I use the export functions in MySQL Workbench or PHPMyAdmin it gives me extra information. Ideally I would like to create something that is one file per table and it either just contains the create statement required to create the table or it contains the insert statement for adding data to the table.

I realize I could use the files generated by export and manually modify them but I have a lot of tables so I am hoping there might be a tool to do this.

How do I use Migration Wizard to create an export? I am trying to use it but I keep getting an error that the source and target schema cannot be the same. 

mysqldump can do a table at a time,

I have never used mysqldump. I will try it today

How do I use mysqldump? How do I access the shell in order to execute the mysqldump command?

I tried using Windows PowerShell but I get mysqldump is not a recognized as the name of a cmdlet, function ....

I have figured out how to use mysqldump. Please ignore my previous question

I see that I can execute a command that generates the create statement for a single table and output it to a file. My database has about 250 tables and I need to generate a create statement for each table and save it to its own file.  Is there a way to create a script that I can execute in the command prompt that I can use to execute the command for each of my tables?

Also, I am doing the following:
mysqldump.exe --no-data --skip-comments --compact --compatible=mysql323 --host=127.0.0.1 -u root --password=my_password my_dbase_name my_dbase_table > my_out.sql

and my output is:
CREATE TABLE `my_dbase_table  ` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `strLabel` varchar(45) DEFAULT NULL,
  `strValue` text,
  `strProvider` varchar(45) DEFAULT NULL,
  `lastModified` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6;

Is there a way to create the output without the: TYPE=MyISAM AUTO_INCREMENT=6

Thank you for all of your help.

Thank you. I had forgotten about .BAT files. I haven't used them in a long time since my DOS days :). That is a perfect solution.

Is there a way to generate the create statement without the extra info after the last right bracket? I want to be able to read each file into my coldfusion code and then execute the code to generate a new database (an empty database with the datasource will have already been created). It seems that the next after the right bracket causes an error in my coldfusion code.

I haven't touched MySQL for a few years so I'm not exactly sure what is in the square brackets.

However, mysqldump has MANY options to include/exclude individual pieces.

I would start with the --opt parameter and look at the individual options it is a shortcut for.  One or more of them should get you the exact output you want.

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_opt

Hi,
In Linux you execute bash script like this to export the ddl statement on a file per table basis

#!/bin/bash 
backupdir=/[YOUR BACKUP_LOCATION] 
for T in `mysql -u [USER] -p[PASSWORD] -N -B -e 'show tables from [DATABASE]'`; do 
mysqldump --skip-comments --compact -u [USER] -p[PASSWORD] [DATABASE] $T > $backupdir/$T.sql 
done;

Select allOpen in new window

You can easilly transform this to a powershell script using the same mysql commands but with powershell logic for the backupdir and list of tables. :)
Also see the note here on the PowerShell and mysqldump on character set handling.
This powershell script generates backup for each database

Set-Location "$MYSQL_HOME\bin"
& .\mysql.exe -N -s -r -u $dbuser -p$dbpass -e 'show databases' | % {  & .\mysqldump.exe -u $dbuser -p$dbpass --single-transaction $_ |    Out-File "$BKP_FOLDER\${_}$BACKUPDATE.sql" -Encoding Ascii
}

Select allOpen in new window

Regards,
   Tomas Helgi