Re: Enumerate columns and tables in a query

Christophe Courtois (christophe_at_courtois.cc) wrote:
: Hi,

: I'm searching to get a list of columns and tables used in a query,
: including columns which appear in select, where, order by clauses, and
: before aliasing happens. (The goal is to search in a huge library of
: SQL scripts where some data is used. Scripts are already stored into
: the database as chars to make the search easier. )

: For example, if the request is :

: SELECT ta.cola AS toto, colb, x.coco
: FROM my_table_a ta,
: my_table_b,
: (SELECT colc AS coco, someid
: FROM my_table_c ) x
: WHERE
: ta.id = x.someid
: AND ta.id IN (SELECT sourceid FROM my_table_d)
: ORDER BY coco, ta.sortid ;

: I want to obtain something like :

: my_table_a cola
: my_table_a id
: my_table_a sortid
: my_table_b colb
: my_table_c colc
: my_table_c someid
: my_table_d sourceid

: I'd like to parse INSERT or UPDATE requests too.

: Explain_plan does not help very much, as it only gives the involved
: tables. I would'nt like to rewrite a SQL-parser, somebody must have
: done it before...

There is a perl module on cpan that parses sql.

        www.cpan.org

Either SQL::Statement or SQL::Parser.

I've never used it, so I don't know if it would help. Received on Tue May 13 2003 - 15:39:42 CDT