RE: duplicating an existing table
Here is another Perl script to do the same job:
#!/usr/local/bin/perl -w
#create an empty table using existing table (work with dbschema)
#dbschema -d db_name -t old_table_name|rename.pl new_table_name
use strict;
my $newTable=$ARGV[0];
my $newFile=$newTable.".sql";
my $oldTable;
my $oldOwner;
my $line;
my $cnt = 0;
open OUT, ">$newFile" or die;
while(defined($line=<STDIN>)) {
next if $cnt++ < 5 ; #ignore the first 5 lines
next if $line =~ /^\\s*$/ ; #ignore blank lines afterwards
($oldOwner=$1,$oldTable=$2) if $line =~/^create table (.+\\.)(.+) $/i;
$line =~ s/$oldOwner//g; # delete old table owner if you like
$line =~ s/$oldTable/$newTable/g;
$line =~ s/constraint.*,$/,/g; #delete constraint name if you like
print OUT $line;
}
close OUT;
Peter
On Tuesday, November 16, 1999 8:25 AM, Art S. Kagel [SMTP:kagel@bloomberg.net] wrote:
> makojoe wrote:
> >
> > How can I duplicate an existing table? (I don't need the data inside, I
> > just want another table with the same schema). I know I can dump the
> > schema with dbschema and edit the file to change the table name, but I
> > was wondering if there's a shortcut using SQL.
>
> No but here is an awk script that you can use with either dbschema or
> myschema.ec:
>
> #rename.awk
> /create table/{
> if ( $3 == "*.*" ) {
> split($3, a, "." );
> printf( "%s.%s\\n", a[1], NewName );
> } else {
> printf( "create table %s\\n", NewName );
> }
> next;
> }
> /CREATE TABLE/{
> if ( $3 == "*.*" ) {
> split($3, a, "." );
> printf( "%s.%s\\n", a[1], NewName );
> } else {
> printf( "CREATE TABLE %s\\n", NewName );
> }
> next;
> }
> {
> print $0;
> }
>
>
> You execute this by setting NewName on the commandline, thus:
>
> dbschema -d mydbs -t oldtable |awk -v NewName=new_table_name -f rename.awk>
>
> Art S. Kagel