Extract table names from SQL script

Toad for Oracle & DevOps Toolkit
1

Is there a way to quickly extract all of the table names used in a long SQL script file?

2

I have no solution, but i could use it, too.

Maybe, you make an entry in “Idea Pond”.

Regards

Even

3

Why do you need that?

I use this for search for some “table_name” in text … but this is search for specific table name…not any name…

select regexp_count(replace(replace(replace(replace(replace(replace (replace(:sql_text,’,’,’,’),’ ‘,’,’),’.’,’,’),’>’,’,’),’=’,’,’),’(’,’,’),’)’,’,’) ,’(^|,)’||p_table_name||’(,|$)’,1,‘i’) from dual;

where sql_text is your script text. it has problems with $ names…

Example:

SQL>exec :sql_text := Q’[‘BEGIN SELECT username INTO my_username FROM v$session WHERE audsid = userenv(‘SESSIONID’); SELECT 1 FROM DUAL WHERE 1=2; END;’]’;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL>select regexp_count(replace(replace(replace(replace(replace(replace (replace(:sql_text,’,’,’,’),’ ‘,’,’),’.’,’,’),’>’,’,’),’=’,’,’),’(’,’,’),’)’,’,’) ,’(^|,)’||‘dual’|
|’(,|$)’,1,‘i’) nr_occurances from dual;

NR_OCCURANCES

        1

Elapsed: 00:00:00.00
SQL>select regexp_count(replace(replace(replace(replace(replace(replace (replace(:sql_text,’,’,’,’),’ ‘,’,’),’.’,’,’),’>’,’,’),’=’,’,’),’(’,’,’),’)’,’,’) ,’(^|,)’||‘v$sess
ion’||’(,|$)’,1,‘i’) nr_occurances from dual;

NR_OCCURANCES

        0

Elapsed: 00:00:00.00
SQL>

4

For me, it would be useful during performance tuning.

If I have a complex statement to tune, sometimes I check segement size and statistic values of the used tables querying the user-views.

5

Use Code Analysis. In the Editor there’s a button on the toolbar to “Analyze code in current editor window.” Selecting that results in a popup with code statistics including CRUD matrix. Sample output shown below.

Executable lines of code: 0
Number of comments: 0
TCR: 1
Halstead Volume: 1
McCabe’s Cyclomatic: 1
Maintainability Index: 100.00
Code Analysis total rule violations from Top 20: 8
Number of unique Code Analysis rules violated: 1 of 20

CRUD Matrix:

Create : 0
Retrieve : 8
Update : 0
Delete : 0

CRUD Matrix Details:

Table Create Retrieve Update Delete
SYS.CON$ 0 2 0 0
SYS.USER$ 0 2 0 0
SYS.CDEF$ 0 2 0 0
SYS.OBJ$ 0 2 0 0

6

and this example, also working…

17:13:58 SQL>var sql_text VARCHAR2 (4000);
17:13:58 SQL>
17:13:58 SQL>exec :sql_text := Q’[‘BEGIN SELECT * FROM PCT_PARAMS, SRV_FTR_BB WHERE 1=0’]’;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:13:58 SQL>
17:13:58 SQL>select regexp_count(replace(replace(replace(replace(replace(replace (replace(:sql_text,’,’,’,’),’ ‘,’,’),’.’,’,’),’>’,’,’),’=’,’,’),’(’,’,’),’)’,’,’) ,’(^|,)’||‘PCT_PA
RAMS’||’(,|$)’,1,‘i’) nr_ocurances from dual;

NR_OCURANCES

       1

Elapsed: 00:00:00.00
17:13:58 SQL>
17:13:58 SQL>select regexp_count(replace(replace(replace(replace(replace(replace (replace(:sql_text,’,’,’,’),’ ‘,’,’),’.’,’,’),’>’,’,’),’=’,’,’),’(’,’,’),’)’,’,’) ,’(^|,)’||‘SRV_FT
R_BB’||’(,|$)’,1,‘i’) nr_ocurances from dual;

NR_OCURANCES

       1

Elapsed: 00:00:00.01
17:13:58 SQL>17:13:58 SQL>var sql_text VARCHAR2 (4000);
17:13:58 SQL>
17:13:58 SQL>exec :sql_text := Q’[‘BEGIN SELECT * FROM PCT_PARAMS, SRV_FTR_BB WHERE 1=0’]’;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:13:58 SQL>
17:13:58 SQL>select regexp_count(replace(replace(replace(replace(replace(replace (replace(:sql_text,’,’,’,’),’ ‘,’,’),’.’,’,’),’>’,’,’),’=’,’,’),’(’,’,’),’)’,’,’) ,’(^|,)’||‘PCT_PA
RAMS’||’(,|$)’,1,‘i’) nr_ocurances from dual;

NR_OCURANCES

       1

Elapsed: 00:00:00.00
17:13:58 SQL>
17:13:58 SQL>select regexp_count(replace(replace(replace(replace(replace(replace (replace(:sql_text,’,’,’,’),’ ‘,’,’),’.’,’,’),’>’,’,’),’=’,’,’),’(’,’,’),’)’,’,’) ,’(^|,)’||‘SRV_FT
R_BB’||’(,|$)’,1,‘i’) nr_ocurances from dual;

NR_OCURANCES

       1

Elapsed: 00:00:00.01
17:13:58 SQL>