Comparing Oracle table structure - Constraints

In an earlier post we compared Oracle table columns - Data Type and Data Length. Now we will try to compare the constraints between 2 tables. Below is the perl program which will compare the table constraints. In the Editable Section of the script you may provide DBA user, schema and table information.

use DBI;
use Data::Dumper;
use List::MoreUtils qw(each_array);

##### Editable section #####
## DB information for 1st table #####
my $tns1="ora10g";
my $dbauser1="system";
my $dbapass1="motive";
my $schema1="TEST1";
my $table1 = "BOOKS1";

## DB information for 2nd table #####
my $tns2="ora10g";
my $dbauser2="system";
my $dbapass2="motive";
my $schema2="TEST2";
my $table2 = "BOOKS2";
##### End Edit section #####

my (%cons_cols1, %cons_cols2);

my $dbh1=DBI->connect("dbi:Oracle:$tns1" , $dbauser1 , $dbapass1) || die "db connect fail to $tns1 \n";
my $dbh2=DBI->connect("dbi:Oracle:$tns2" , $dbauser2 , $dbapass2) || die "db connect fail to $tns2 \n";

%cons_cols1 = &getConsColumns($dbh1, $schema1, $table1);
%cons_cols2 = &getConsColumns($dbh2, $schema2, $table2);

my @col1 = keys %cons_cols1;
my @col2 = keys %cons_cols2;
print "$schema1-$table1 has constraints in columns: @col1\n";
print "$schema2-$table2 has constraints in columns: @col2\n";

#print Dumper(\%cons_cols1);
# print Dumper(\%cons_cols2);

print "\n========================================================================\n";
printf "%-20s| %-20s |%-15s\n", "$schema1\.$table1", "\tDESCRIPTION", "\t$schema2\.$table2";
print "------------------------------------------------------------------------\n";

for my $k (keys %cons_cols1){
 unless(($cons_cols1{$k}{constraint_name} eq $cons_cols2{$k}{constraint_name}) && ($cons_cols1{$k}{constraint_type} eq $cons_cols2{$k}{constraint_type})) {
  printf "%-20s| %-20s |%-15s\n", "$k($cons_cols1{$k}{constraint_name})($cons_cols1{$k}{constraint_type})", "\tCons different", "\t$k($cons_cols2{$k}{constraint_name})($cons_cols2{$k}{constraint_type})";
  delete($cons_cols1{$k});
  delete($cons_cols2{$k});
 }
}
for my $k (keys %cons_cols2){
 unless(($cons_cols1{$k}{constraint_name} eq $cons_cols2{$k}{constraint_name}) && ($cons_cols1{$k}{constraint_type} eq $cons_cols2{$k}{constraint_type})) {
  printf "%-20s| %-20s |%-15s\n", "$k($cons_cols1{$k}{constraint_name})($cons_cols1{$k}{constraint_type})", "\tCons different", "\t$k($cons_cols2{$k}{constraint_name})($cons_cols2{$k}{constraint_type})";
  delete($cons_cols1{$k});
  delete($cons_cols2{$k});
 }
}
print "------------------------------------------------------------------------\n";
print "Note:\n \tC (check constraint) \n \tP (primary key) \n \tU (unique key) \n \tR (referential integrity)\n";
print "------------------------------------------------------------------------\n";

## Get only those columns which have constraints
sub getConsColumns {
 my $db = $_[0];
 my $schemaName = $_[1];
 my $tableName = $_[2];
 my (%cols, $rec);

 my $sqlstr = "select acc.COLUMN_NAME, acc.CONSTRAINT_NAME, ac.CONSTRAINT_TYPE from ALL_CONS_COLUMNS acc, ALL_CONSTRAINTS ac ".
  " where acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME and ac.table_name=acc.table_name and ac.OWNER = '$schemaName' and ac.TABLE_NAME = '$tableName'";
 my $sth=$db->prepare($sqlstr);
 $sth->execute;
 for $rec (@{$sth->fetchall_arrayref}) {
  $cols{$rec->[0]}{constraint_name} = $rec->[1];
  $cols{$rec->[0]}{constraint_type} = $rec->[2];
  }
 $sth->finish;
 return %cols;
}

Save the program as "CompareTable2_Cons.pl". A sample run of the program is below. In the output the constraint name and type are included with each columns. E.g. PRICE is column-name. NEW_UNIQUE is constraint-name, C is constraint type.