Cross Referencing script

Script to cross reference sybase tables and stored procedures of a given database with perl scripts (.cgi, .pl, .pm extensions)

  #!/ms/dist/perl5/bin/perl5.6

use strict;
use Sybase::DBlib;
use Benchmark;

my $t1 = new Benchmark;

if( scalar (@ARGV) < 2 ) {
 die "Need the directory of perl scripts and/or DB name!\n";
}

my $dir = $ARGV[ 0 ];
my $dbname = $ARGV[ 1 ];
my $user = $ARGV[ 2 ];
my $pass = $ARGV[ 3 ];
my $server = $ARGV[ 4 ];

my $dbh = new Sybase::DBlib( $user, $pass, $server ) or die "\nCannot login to $server\n";
my $db = $dbh->dbuse( $dbname );

opendir DIR, $dir or die "Cannot open directory $dir for read\n";

my @files = readdir( DIR );

closedir DIR;

my $sql = "select tab.name  from sysobjects tab where tab.type='U'";

my @tab;
my @col;

#Replace the nsql function to call using HASH

my $rc = $dbh->nsql( $sql, "ARRAY", sub{
          push @tab, $_[ 0 ];
          push @col, $_[ 1 ];
         });

if( $DB_ERROR ) {
 die "Error encountered during select of table names- $DB_ERROR\n";
}

my %TblHash;
my $temp;

foreach my $tab1 ( @tab ) {
 my $sql;

 $sql = "sp_depends $tab1";

 my @depends;

 $dbh->nsql( $sql, "ARRAY", sub{
         if(( defined($_[ 1 ] )) and ($_[ 1 ] eq "stored procedure" )){
          my $proc1 = substr($_[ 0 ],4);
          $proc1 =~ s/\s+//g;
          push @depends, $proc1;
         }
         });

 print "Searching for table - $tab1\n";

 foreach my $fl1 ( @files ) {
  if( $fl1 =~ 'cgi$|perl$|pm$|perl$' ) {

   open (FIL, "$dir/$fl1") or warn "cannot open - $fl1\n";
    
   my $line;

   while( $line =  ) {

    if( $line =~ $tab1 and $line !~ '^#') {
     if( $TblHash{$tab1} !~ $fl1 ) {
       $TblHash{$tab1} = $TblHash{$tab1}."$fl1,";
     }
    }
    else {
     foreach (@depends) {
      if( $line =~ $_ ) {
       if( $TblHash{$tab1} !~ $fl1 ) {
         $TblHash{$tab1} = $TblHash{$tab1}."$fl1,";
       }
      }
     }
    }
   }
   close FIL;
  }
 }
}     

open OUT,">report.txt" or die "cannot open report for write!\n";

foreach (keys (%TblHash)) {
 my @scriptfiles = split /,/,substr($TblHash{$_},0,-1);

 print OUT "\n--------------------------\n";
 print OUT $_,"\n--------------------------\n";

 foreach (@scriptfiles){
  print OUT $_,"\n";
 }

 print OUT "\n++++++++++++++++++++++++++\n\n";
}

close OUT;


$dbh->dbclose();

my $t2 = new Benchmark;

my $timediff = timediff( $t2, $t1 );

print "Total time for exec - ",timestr($timediff),"\n";