Clean up CREATE TABLE column layout

Question

I've got a series of sql scripts that look like this:

CREATE TABLE table_one
(
    column_one            int   not null,
    column_two    varchar(100)               not null,
    column_three_four_five int,
    column_six    decimal(16,4) null,
    PRIMARY KEY ( column_one, column_three_four_five)
);

I'd like to clean up the layout to be easier to scan, something like this:

CREATE TABLE table_one
(
    column_one             int          not null,
    column_two             varchar(100) not null,
    column_three_four_five int,
    column_six             decimal(16,4)     null,
    PRIMARY KEY
    (
        column_one,
        column_three_four_five
    )
);

The exact layout is less important than being able to create a clean look to improve readability. (read: please don't flame the formatting itself) -grin-

What would be a good way to script this (I'm looking at you, Perl gods...)?

Answer 1

http://www.dpriver.com/pp/sqlformat.htm

Not a script. But I don't want to code anything right now.

For stuff like this in any language look for formatters or the word 'pretty' for prebuilt junk.

Answer 2

I haven't tried either one, but CPAN has SQL::Beautify and SQL::QueryBuilder::Pretty.

Answer 3

Hmmmmm well can't say it will work for all your files but something like this would do the job ...

#!/usr/bin/perl
use strict; 
use warnings;

my $default_padding = 30;
my $my_file = 'data.sql';
my $my_new_file = 'data_new.sql';

open (my $fh, '<', $my_file) or die $!;
my @sql = <$fh>;
close($fh);

open (my $fhsave, '>>', $my_new_file) or die $!;
foreach my $line (@sql) {
    print $fhsave "$line" if ($line !~ /^\s+/);
    $line =~ s/\s+/ /ig;
    print $fhsave sprintf("   %-*s %s\n", $default_padding, $1, $2) if ($line =~ /^\s+(.+?)\s+(.+)/);
}
close ($fhsave);

sample file data.sql

CREATE TABLE table_one
(
    column_one            int   not null,
    column_two    varchar(100)               not null,
    column_three_four_five int,
    column_six    decimal(16,4) null
);

output:

CREATE TABLE table_one
(
   column_one                     int not null, 
   column_two                     varchar(100) not null, 
   column_three_four_five         int, 
   column_six                     decimal(16,4) null 
);