Re: Enumerate columns and tables in a query

Christophe Courtois 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...
> Any bit of code, idea, pre-made package, dba table, existing (cheap!)
> tool... would be welcome.
>
> NB : Column names are often identical across tables (attribute1,
> attribute2... these are Oracle Applications user flexfields), which
> make 'grep' or 'like' + column_name useless.
>
> Thank you very much !

If you are asking about scripts stored outside the database it can not be done unless your code is remarkably pedestrian.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)