In general, this isn't possible from SQL alone -- you have to look at the database.

Given table foo containing columns (id, a, b) and table bar containing columns (id, c, d), what would you expect your program to return from this SQL?

SELECT a, b, c, d from foo, bar where foo.id = bar.id;
[download]

SELECT * from foo, bar where foo.id = bar.id;
[download]

Of course, your codebase may not contain these types of statement.

Good points. SQL::Statement only checks those kinds of things on execute(), not on prepare(). For example it dies on prepare() if you use a column name that isn't a valid SQL identifier but prepare() succeeds if you use a non-existant column name - it only finds that out later. For example, it errors if a column name exists in two tables and your SQL doesn't specigy which table the name refers to, but it only checks for that and dies on execute(). If you have the database available and wanted to use it to check the column names without actually executing anything you'd probably be able to just grab the parts of execute() that check the database tables for actual column names.

update :Another option might be to create a hash structure of the table/columns you expect to encounter and tweak the return from SQL::Statement to fill in table information from that when the module was not able to get it from the SQL.

I tried to read all the code but because I am a beginner I was not able to understand it much. With SQL::Parser, Data::Dumper showed that things were parsed well on the sample commands I tried, however I don't understand how the data structures are designed so I don't know how to write methods to grab the relevant information. Can you please give a short explanation or just a list of the data structures (like where_cols, etc.)?

Thanks a lot!

Here ya go:

#!/usr/bin/perl
use warnings;
use strict;
use SQL::Statement;

my $stmt = SQL::Statement->new("
    SELECT a, b FROM tblA LEFT JOIN tblB WHERE c > 10 and tblB.d = 5
");
printf "Tables      : %s\nSelect Cols : %s\nWhere Cols  : %s\n",
    join( ', ', @ { $stmt->{org_table_names} } ),
    join( ', ', map {$_->display_name} $stmt->columns ),
    join( ', ', keys %{ $stmt->{where_cols} } ),
;

# OUTPUT
#    Tables      : tblA, tblB
#    Select Cols : a, b
#    Where Cols  : tblB.d, c
[download]

Note, that as menolly++ mentioned, columns are only associated with tables if explicitly specified in the SQL.

And on a different (off) topic : Congratulations! You are customer number 1,000 at jZedHacksRus. Pick up your door prize on the way out.