print "\nThis program reformats scripts produced by SQL Server 2000 Enterprise Manager\n";
print "to remove brackets and tab out data types and null settings.\n\n";
print "You provide a file name, this program reads it and produces a new file\n";
print "with a .out extension.\n\n";
print "File name to process? (<enter> to end program.) ";

chomp($sqlfile = <stdin>);

$outfile = $sqlfile . ".out";

$datatypes = " binary/ bigint/ bit/ char/ datetime/ "
	. " decimal/ float/ image/ int/ money/ "
	. " nchar/ ntext/ nvarchar/ numeric/ real/ "
	. " smalldatetime/ smallint/ smallmoney/ sql_variant/ "
	. " sysname/ text/ timestamp/ tinyint/ varbinary/ varchar/ "
	. " uniqueidentifier";

open(IN,  $sqlfile)    || die "cannot open $sqlfile for input: $!";
open(OUT, ">$outfile") || die "cannot open $outfile for output: $!";

while (<IN>) {
        chomp;

	#delete or comment out the two following lines
	#if you need brackets around your object names.
	s/\[//g;
	s/\]//g;
	s/ \(/\(/g;
	s/ \,/\,/g;
	s/COLLATE SQL_Latin1_General_CP1_CI_AS//g;
	s/ON PRIMARY//g;

	s/\sbinary/\t\t\tbinary/g;
	s/\sbigint/\t\t\tbigint/g;
	s/\sbit/\t\t\tbit/g;
	s/\schar/\t\t\tchar/g;
	s/\sdatetime/\t\t\tdatetime/g;
	s/\sdecimal/\t\t\tdecimal/g;
	s/\sfloat/\t\t\tfloat/g;
	s/\simage/\t\t\timage/g;
	s/\sint/\t\t\tint/g;
	s/\smoney/\t\t\tmoney/g;
	s/\snchar/\t\t\tnchar/g;
	s/\sntext/\t\t\tntext/g;
	s/\snvarchar/\t\t\tnvarchar/g;
	s/\snumeric/\t\t\tnumeric/g;
	s/\sreal/\t\t\treal/g;
	s/\ssmalldatetime/\t\t\tsmalldatetime/g;
	s/\ssmallint/\t\t\tsmallint/g;
	s/\ssmallmoney/\t\t\tsmallmoney/g;
	s/\ssql_variant/\t\t\tsql_variant/g;
	s/\ssysname/\t\t\tsysname/g;
	s/\stext/\t\t\ttext/g;
	s/\stimestamp/\t\t\ttimestamp/g;
	s/\stinyint/\t\t\ttinyint/g;
	s/\svarbinary/\t\t\tvarbinary/g;
	s/\svarchar/\t\t\tvarchar/g;
	s/\suniqueidentifier/\t\t\tuniqueidentifier/g;

	s/NOT NULL/\t\tNOT NULL/s;
	s/NULL/\t\tNULL/s;

	print "$_\n";
	print OUT "$_\n";
}

close(OUT) || die "problem closing new $outfile: $!";
close(IN)  || die "problem closing original $sqlfile: $!";