List:General Discussion« Previous MessageNext Message »
From:John Doe Date:April 11 2005 12:42pm
Subject:Re: Auto SQL
View as plain text  
Hi Winanjaya

Am Montag, 11. April 2005 09.50 schrieb Winanjaya:
> I am using MySQL 4.1, I have a database with more than 100 tables inside,
> is there any 3rd party tools that can help me to create a SQL statement of
> "CREATE TABLE blah blah blah" for each table in my DB . please advise

Here's a quick and dirty "3rd party" tool doing what you want.

- minimal error checking
- pay attention to the fact that the password is in the script
- not the best/elegant/fastest coding

After configuring db access and making the script executable, you call it e.g. 
like

   $ script > file_with_create_table_statements

greetings joe

=== begin script ===

#!/usr/bin/perl

# This quick'n'dirty (!!) script outputs the CREATE TABLE 
# statements of all tables in a mysql database

use strict; use warnings;

use DBI;

# ADJUST $db to $password to your needs
#
my $dbh=DBI->connect(
 "DBI:mysql:$db:$host:$port",
 $username, $password,
 {RaiseError=>1}
 )
 or die "$0: $DBI::errstr"; }


# get the list of tables into arrayref $tables
#
my $out=$dbh->prepare("show tables") or die;
$out->execute or die;
my $tables=$out->fetchall_arrayref;

# output CREATE TABLE statement for every table
#
foreach my $t (@$tables) {
 my $out2=$dbh->prepare("show create table @{[ $t->[0] ]}") or die;
 $out2->execute or die;
 print $out2->fetchrow_array, ";\n";
}

=== end script ===
Thread
Auto SQLWinanjaya11 Apr
  • Re: Auto SQLJigal van Hemert11 Apr
    • RE: Auto SQLWinanjaya11 Apr
  • Re: Auto SQLMartijn Tonies11 Apr
  • Re: Auto SQLJohn Doe11 Apr
  • Re: Auto SQLWolfram Kraus11 Apr