2

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?

tuxian
  • 159
  • 5
  • 12
  • 2
    you could maybe get some of the detail from the explain plan of the query. – OldProgrammer Mar 17 '18 at 18:59
  • To get a list of referenced tables from query see [here](https://stackoverflow.com/questions/49157492/oracle-search-list-of-words-in-string-and-retruen-existing-ones/49183459#49183459). Do you mean returned columns (easy from cursor metadata) or really *used* columns (check `Column Projection Information` in execution plan). – Marmite Bomber Mar 17 '18 at 20:06
  • `TO_DATE` and `TO_CHAR` aren't columns: they are functions. – Borodin Mar 17 '18 at 21:45
  • Hi Marmite Bomber, Could you please tell me about column Projection Information and how to get that? – tuxian Mar 20 '18 at 05:18

1 Answers1

2

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;
eifla001
  • 1,137
  • 8
  • 8
  • Hi, Thanks! using this approach I can retrieve the tables name used, but how about the columns from those tables? – tuxian Mar 20 '18 at 05:19
  • 1
    Haha, I don't know how to get the columns. But I added a query in my answer, maybe it can help you. – eifla001 Mar 20 '18 at 05:47