# Extract table names from SQL script

**URL:** <https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [April 18, 2017, 5:02am UTC](https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347 "2017-04-18T05:02:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dale.Raar](https://avatars.discourse-cdn.com/v4/letter/d/da6949/32.png) [@Dale.Raar](https://forums.toadworld.com/u/Dale.Raar)\
**Post date:** [April 18, 2017, 5:02am UTC](https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347/1 "2017-04-18T05:02:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![even.langer](https://avatars.discourse-cdn.com/v4/letter/e/51bf81/32.png) [@even.langer](https://forums.toadworld.com/u/even.langer)\
**Post date:** [May 1, 2017, 11:57pm UTC](https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347/2 "2017-05-01T23:57:21Z")

</div>

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

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

Regards

Even

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [May 2, 2017, 7:59am UTC](https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347/3 "2017-05-02T07:59:27Z")

</div>

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\>

---

<div class="post-metadata">

**Author:** ![even.langer](https://avatars.discourse-cdn.com/v4/letter/e/51bf81/32.png) [@even.langer](https://forums.toadworld.com/u/even.langer)\
**Post date:** [May 2, 2017, 8:08am UTC](https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347/4 "2017-05-02T08:08:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\
**Post date:** [May 2, 2017, 8:14am UTC](https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347/5 "2017-05-02T08:14:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [May 2, 2017, 8:15am UTC](https://forums.toadworld.com/t/extract-table-names-from-sql-script/39347/6 "2017-05-02T08:15:03Z")

</div>

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\>