PHP script to export table creation SQL from MySQL

I was trying to export the structure of a MySQL database using phpMyAdmin but it kept timing out and not showing me the full create script. I think phpMyAdmin uses the information schema to get this information and its on a host with hundreds of databases and tables so querying the information schema runs very slowly. Instead I knocked together a quick PHP script to dump the structure instead and share it here.

The PHP script

Substitute the username, password, hostname and database values so they are appropriate for you. Then call the script from a web browser (you may want to put it in a password protected directory) and it will show the create queries which can then be run directly in MySQL.

set_time_limit(0);

$username = 'XXXXXX';
$password = 'YYYYYY';
$hostname = 'ZZZZZZ';
$database = 'AAAAAA';

try {
	$pdo = new PDO("mysql:host={$hostname};dbname={$database}", $username, $password);
}
catch(PDOException $e) {
	die("Could not connect to the databasen");
}

echo '<pre>';
$stmt1 = $pdo->query('SHOW TABLES', PDO::FETCH_NUM);
foreach($stmt1->fetchAll() as $row) {
	$stmt2 = $pdo->query("SHOW CREATE TABLE `$row[0]`", PDO::FETCH_ASSOC);
	$table = $stmt2->fetch();
	echo "{$table['Create Table']};nn";
}
echo '</pre>';

The script itself is fairly self explanatory.

Note that the table name is enclosed with backticks; this prevents SQL errors if the table is named with a reserved word, which happened to me when I was writing the script.

The bit that echoes the create query terminates with a semi-colon; without this it’s all one query and will fail if you try to run it from phpMyAdmin or the MySQL command line.