Enumerate columns and table in a request

From: Christophe <chcourto_at_cgey.com>
Date: 13 May 2003 04:10:51 -0700
Message-ID: <8f591822.0305130310.69da6f35_at_posting.google.com>

Hi,

 I'm searching to get a list of columns and tables used in a request, 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... 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:10:51 CEST