Easliy Compare Oracle Table Structure - Column

There are various tools available in market for comparing objects in Oracle. You can also write down a SQL query to do the same, which might be bit lengthy depending upon you requirements.
Comparing table structures in Oracle can be easier using some scripting language. Various attributes could be compared between 2 tables. To start with, here is a Perl script which compares 2 tables, placed in different schema or in the same schema.
The Perl script uses DBD::Oracle, and List::MoreUtils modules. There is an Editable Section in the script where you can provide the DBA credentials and the respective tables to be compared. The DBA user should have access to the schema tables. The script compares the number of columns and prints out the unique ones, it will then compare the Data Type and Data Length of the similar columns. We can extend it to include other parameters if required.
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 #####

## Declare hash vars to hold the columns of the 2 tables
my (%columns1, %columns2);

## Get connection to each DB
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";

## Get the columns of the tables in the variables
%columns1 = &getTableCols($dbh1, $schema1, $table1);
%columns2 = &getTableCols($dbh2, $schema2, $table2);

##Check with Data Dumper
#print Dumper(\%columns1);
#print Dumper(\%columns2);

my @uniqdbcol1 = &getUniqKeys(\%columns1, \%columns2);
my @uniqdbcol2 = &getUniqKeys(\%columns2, \%columns1);

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

## Print out the unique columns in both the tables
## Also delete the elements from columns hash, we will not compare them
my $iter = each_array(@uniqdbcol1, @uniqdbcol2);
while ( my ($i, $j) = $iter->() ) {
    $i=''  unless defined($i);
 $j=''  unless defined($j);
 printf "%-20s| %-20s |%-15s\n", "$i", "\tUnique Column", "\t$j";
print "------------------------------------------------------------------------\n";

## Print the columns with different data types.
## Also remove the elements from the hash
for my $key (keys %columns1) {
 unless ( exists $columns2{$key} ) {
 unless ($columns1{$key}{data_type} eq $columns2{$key}{'data_type'}) {
  printf "%-20s| %-20s |%-15s\n", "$key($columns1{$key}{data_type})", "\tdata type diff", "\t$key($columns2{$key}{data_type})";
print "------------------------------------------------------------------------\n";

##Print columns with different data lengths.
for my $key (keys %columns1) {
 unless ( exists $columns2{$key} ) {
 unless ($columns1{$key}{data_length} eq $columns2{$key}{'data_length'}) {
  printf "%-20s| %-20s |%-15s\n", "$key($columns1{$key}{data_length})", "\tdata length diff", "\t$key($columns2{$key}{data_length})";
print "\n========================================================================\n";

#Subroutine to get the columns, data type, length in hash
sub getTableCols {
 my $db = $_[0];
 my $schemaName = $_[1];
 my $tableName = $_[2];
 my (%cols, $rec);
 my $sqlstr = "select column_name, data_type, data_length from ALL_TAB_COLUMNS".
  " where owner = '$schemaName' and table_name='$tableName' order by column_name";
 my $sth=$db->prepare($sqlstr);
 for $rec (@{$sth->fetchall_arrayref}) {
  $cols{$rec->[0]}{data_type} = $rec->[1];
  $cols{$rec->[0]}{data_length} = $rec->[2];
 return %cols;

## Get the unique elements as array from the first hash passed to the subroutine.
sub getUniqKeys {
 my %hash1 = %{$_[0]};
 my %hash2 = %{$_[1]};
 my @uniq = ( );
 foreach (keys %hash1) {
        push(@uniq, $_) unless exists $hash2{$_};
 return @uniq;

Save the program as "CompareTable1_Cols.pl".
A sample run of the program is below:

No comments:

Post a Comment