Enumerate columns and tables in a query

From: Christophe Courtois <christophe_at_courtois.cc>
Date: Tue, 13 May 2003 20:03:29 +0200
Message-ID: <b9rc1h$2s68$1@biggoron.nerim.net>

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 ! Received on Tue May 13 2003 - 13:03:29 CDT