Get the table names used in an SQL query

I had cause to analyse a query for the table names used in it before sending it off to the database. I don’t actually do it this way any more. But this is a solution using the SQL::Statement module from Cpan in perl

#
# First fetch table names from query
#
my $sql = “SELECT blah FROM blah, blah, blah”;

# Using AnyData rather than ANSI because ANSI catches reserved
# words even if they are quoted or fully qualified (bug in SQL::Parser)
my $parser = SQL::Parser->new(‘AnyData’);
$parser->{RaiseError} = 0;
$parser->{PrintError} = 1;

# Parse the table names from the query and clean them up.
my $statement = SQL::Statement->new($sql,$parser);
foreach my $table ($statement->tables())
{
    # remove quotes (") from table name and convert to lower case
    my ($table_name_clean) = lc($table->name());
    $table_name_clean =~ s/"//g;

    push(@table_names, $table_name_clean);
}

Post a Comment | Trackback URI

New! We've just started a message board and discussion forum over on the Toxic Byte Technical Forum. It's a bit lonely over there at the moment, but feel free to join in and participate!