I have nearly 800 SQL scripts ranging medium to complex SQLs. Database is Oracle.

I need list of tables used in each of these scripts. Each script contains numerous with clauses, joins and subqueries. Is there a easier way to achieve this?

Thanks LN

CodePudding user response：

first store all queries to 1 table -scripts- with the columns - script_name, script_text then this could work:

select script_name,  script_text, object_name
  from scripts s
  join dba_objects do
    on 1 = 1
 where 1 = 1
   and instr(upper(s.script_text), upper(do.object_name)) > 0

CodePudding user response：

You may try explain plan for each of the statements and then check the content of plan_table. But it:

• doesn't count views (plan table shows base tables of the view) and scalar functions
• should be adapted for dblinks
• should be thoroughly tested with local PL/SQL declarations (with function) and functions in other schemas
• has influence of query rewrite and baselines

Below is an example:

begin
  for i in 1..10 loop
    execute immediate
      'create table t'
      || i || '( id int)';
  end loop;
end;
/

create view v_test
as
select *
from t5
  join t6
  using(id)

create function f_tab
return sys.odcinumberlist
pipelined
as
begin
  null;
end;
/

explain plan for
select *
from t1, t2, t3, t4,
  /*View*/
  v_test,
  /*Pipelined function*/
  table(f_tab())

select
  object_owner,
  object_name,
  object_type
from plan_table
where
  (object_owner, object_name) in (
    select
      f.owner,
      f.object_name
    from all_objects f
    where f.object_type in (
      'TABLE'
      , 'VIEW'
    )
  ) or (object_name, object_type) in (
    select
      f.object_name,
      'PROCEDURE'
    from user_objects f
    where f.object_type in (
      'FUNCTION'
      , 'PROCEDURE'
    )
  )
order by 1

OBJECT_OWNER                | OBJECT_NAME | OBJECT_TYPE
:-------------------------- | :---------- | :----------
FIDDLE_TQYMTNVUFUWHRWJEENKX | T1          | TABLE      
FIDDLE_TQYMTNVUFUWHRWJEENKX | T2          | TABLE      
FIDDLE_TQYMTNVUFUWHRWJEENKX | T3          | TABLE      
FIDDLE_TQYMTNVUFUWHRWJEENKX | T4          | TABLE      
FIDDLE_TQYMTNVUFUWHRWJEENKX | T5          | TABLE      
FIDDLE_TQYMTNVUFUWHRWJEENKX | T6          | TABLE      
null                        | F_TAB       | PROCEDURE  

db<>fiddle here

Anyway, I think that this sort of task should be addressed to SQL parser in any way instead of any string manipulation, because only parser exactly knows what objects were used