# # $Id: //websites/unixwiz/newroot/techtips/fixschemas.txt#2 $ # # written by : Stephen J Friedl 2002/06/07 # Software Consultant # Tustin, California USA # steve@unixwiz.net # # ========================================================= # This program is in the public domain and may be used in # any way without restrictions. # ========================================================= # # # This program processes schema output from Sybase Adaptive # server as produced by the "sp_columns" stored procedure by # making them much easier to read. The output from sp_columns # is in an unload format, and we simply rewrite it to be in a # tabular format more suited for human consuption. # # ALL the input files must end in .SQL (either case), and # though this doesn't seem like a terribly fitting form - it's # not really SQL - it's not important enough to give that much # thought to. The output files always end in .SCH (for schema). # # To run this program: # # perl -w fixschemas.p schemas\*.sql # # and it processes every file from .SQL to .SCH # # use strict; $0 =~ s|.*[\\/]||; # dump leading part of path my @FILES = (); foreach ( @ARGV ) { if ( m/^--help/i ) { print STDERR < .SCH die "SERIOUS ERROR with outfile extension" if $fname eq $outfile; print "Rewriting $fname to $outfile\n"; open(IF, "<$fname") or die "ERROR: cannot open $fname\n"; open(OF, ">$outfile") or die "ERROR: cannot create $outfile\n"; while ( ) { s/\s+$//; # ignore trailing whitespace next if m/^%/; # ignore comments and stats next if length == 0; # ignore blank lines s/'//g; # we don't like the quotes my($dbname, # database name $creator, # creator of the table $tabname, # table name $colname, # column name $typeid, # numeric type ID $typename, # full type name $width, $precision, $dummy1, $dummy2, $isnull, $domain, $sequence # order within table ) = split( m/,/ ); if ( $. == 1 ) { print OF "# generated ", scalar localtime, "\n"; print OF "Database: $dbname\n"; print OF "Creator : $creator\n"; print OF "Table : $tabname\n"; print OF "\n"; printf OF " %-32s %-10s %5s %5s %s\n", "colname", "type", "width", "prec", "NULL?"; print OF " --------------------------------", " ----------", " -----", " -----", " -----\n"; } printf OF " %-32s %-10s %5d %5d %s\n", $colname, $typename, $width, $precision, $isnull ? 'NULL' : ''; } close IF; close OF; }