Re: Automated detection of different tables with same name

[gellyfish]

Of course you realize that this isn't really a Perl question at all, once you have the answers to two questions: How do I find the databases on a given MS-SQL server instance? and How do I find all the user tables in a MS-SQL database?, which are both answered in documentation that you get with every installation of MS SQL Server, then all you have remaining is a rather trivial Perl problem.

I'm not quite sure why I am giving you this as you seem determined to make no effort to help yourself, but it's Monday and hey I'd only be writing code that I actually get paid for rather than writing code that someone else is getting paid for.

 #!/usr/bin/perl
#

use strict;
use warnings;

use DBI;

my $dbh =
  DBI->connect( 'dbi:ODBC:prism72', 'sa', 'xxxx', { RaiseError => 1, }
+ );

my $sth_master = $dbh->prepare("select [name] from master..sysdatabase
+s");

$sth_master->execute();

my @dbs;
while ( my $row = $sth_master->fetch() )
{
    push @dbs, $row->[0];
}
$sth_master->finish();

my %tables;

foreach my $db (@dbs)
{
    my $sth_obj = $dbh->prepare(<<EOFOO);
select [name] from ${db}..sysobjects
where xtype = 'U'
EOFOO

    $sth_obj->execute();

    while ( my $row = $sth_obj->fetch() )
    {
        my $table = lc $row->[0];
        if ( !exists $tables{$table} )
        {
            $tables{$table} = [];
        }
        push @{ $tables{$table} }, $db;
    }
    $sth_obj->finish();
}

foreach my $table ( keys %tables )
{
    if ( @{ $tables{$table} } > 1 )
    {
        print "table $table is in databases  @{$tables{$table}}\n";
    }
}

/J\