Is there a way to list tables and columns used in a ORACLE query?

Question

I've a huge Oracle query (~20k lines) and I need to list out all the tables and columns used in that query. I've google'd and found few SQL-Parser tools and plugins, but they didn't worked out to me.

Found below perl library, but it is not processing TO_DATE and TO_CHAR columns. it is throwing error.

http://search.cpan.org/~rehsack/SQL-Statement-1.412/lib/SQL/Parser.pm

Is there any other way in which I can list all the tables and columns used in the query?

Answer 1

Not sure if this is the answer but you can create a VIEW using your query.

CREATE VIEW your_view_name
AS SELECT * FROM your_table_or_complex_query;

Then list the tables using the SYS.USER_DEPENDENCIES table.

select *
  from SYS.USER_DEPENDENCIES
 where type = 'VIEW'
   AND REFERENCED_TYPE IN ('VIEW', 'TABLE', 'SYNONYM')
   AND name = '<view_name>';

To list the columns, you can use the query below, (the problem with this query is if a column for example COLUMN1 exists in three tables TAB1, TAB2, TAB3 but in your query it only came from TAB1, the three tables will be shown)

SELECT a.referenced_name, b.column_name
  FROM SYS.USER_DEPENDENCIES A, USER_TAB_COLS B, 
       (SELECT dbms_metadata.get_ddl('VIEW','<view_name>','<schema_name>') ddl_text FROM DUAL) c
 WHERE TYPE = 'VIEW'
   AND REFERENCED_TYPE IN ('VIEW', 'TABLE', 'SYNONYM')
   AND name = '<view_name>'
   AND b.table_name = a.name
   AND INSTR(lower(c.ddl_text), lower(b.column_name)) > 0
   ORDER BY referenced_name;