Parse Table Names From A Bunch Sql Statements

I have an table with thousands of SQL statements in a column called Queries. Any ideas on how to get just the table names from the statements by using a regular expression?

Solution 1:

Were it me, I would tend to try to approach the problem a different way. Rather than writing a SQL parser (which would require much more than a regular expression unless you can guarantee that all the SQL statements using a very tiny subset of the available SQL grammar), I would tend to generate a query plan for each object and then query PLAN_TABLE to see the objects that Oracle has to hit. You'd need to do an additional lookup for index accesses to find out what table the index is defined on but that should be reasonably straightforward.

If you go down this path, however, you'll be retrieving the base tables that your query actually touches rather than whatever views the queries may actually refer to. That is, if you have a query SELECT * FROM view_1 and view_1, in turn, is defined as a query against table_a and table_b, only table_a and table_b will be part of the plan. And you would need to disable query_rewrite for the session if you wanted to prevent the query plans from referencing materialized views if those materialized views were not specifically part of the query.

If, for each query, you do an

EXPLAIN PLAN FOR <<the query>>

you can then

SELECTDISTINCT object_owner, object_name, object_type
  FROM plan_table

to get the list of objects. If OBJECT_TYPE is like INDEX%, you can then use the DBA_INDEXES view (or ALL_INDEXES or USER_INDEXES depending on who owns the objects in question and what level of privileges you have) to determine what table that index is defined on

SELECT table_owner, table_name
  FROM dba_indexes
 WHERE owner =<<object_owner from plan_table>>AND index_name =<<object_name from plan_table>>

So, for example, if I have a view view_1

createor replace view view_1
 asselect*from emp join dept using (deptno)

and a query

select * from view_1;

I can do

SQL> explain plan forselect*from view_1;

Explained.

SQL> ed
Wrote file afiedt.buf

  1SELECTdistinct object_owner, object_name, object_type
  2*FROM plan_table
SQL>/

OBJECT_OWNER                   OBJECT_NAME               OBJECT_TYPE
------------------------------ ------------------------- -------------------------

SCOTT                          DEPT                      TABLE
SCOTT                          PK_DEPT                   INDEX (UNIQUE)
SCOTT                          EMP                       TABLE

This tells me that the query is actually hitting the EMP and DEPT tables. It is also hitting the PK_DEPT index so I can look to see what table that is defined on.

SQL> ed
Wrote file afiedt.buf

  1SELECT table_owner, table_name
  2FROM dba_indexes
  3WHERE owner ='SCOTT'4*AND index_name ='PK_DEPT'SQL>/

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SCOTT                          DEPT

As it turns out, that index is defined on the DEPT table as well, so I know that only the EMP and DEPT tables in the SCOTT schema are going to be involved in the query.