CODEWITHSUNDEEP
sqloracle-database
anand baskar
anand baskar

Reputation: 59

How to find the exact table names in the user Query in Oracle SQL?

In Oracle SQL developer I have a Query in which I have to find all the table names which are starting as 'DIM' and 'FCT' and 'ABC'.

Example:

SELECT * FROM DIM_TABLE,fct_table,abc_table WHERE..

I am trying through regular expression instring and substring.But unable to get the exact end position of the table.

Can you please help me in this?

Upvotes: 0

Views: 231

Answers (2)

PKey
PKey

Reputation: 3841

My approach is as follows:

a. first extract the part of the query that contains table names separated by comma

b. convert comma separated values to rows

c. Filter out the rows by the criteria given 

select * from( 
            select  trim(regexp_substr(REGEXP_SUBSTR('SELECT * FROM DIM_TABLE,fct_table,abc_table WHERE','FROM (.*) WHERE',1,1,null,1),'[^,]+', 1, level) ) value, level
              from dual
             connect by regexp_substr(REGEXP_SUBSTR('SELECT * FROM DIM_TABLE,fct_table,abc_table WHERE','FROM (.*) WHERE',1,1,null,1), '[^,]+', 1, level) is not null
               )
        where 
        value like 'DIM%' or value like 'fct%' or value like 'abc%'

results:

DIM_TABLE
fct_table
abc_table

EDIT 2: ok So now that you have changed your original query this will look like the following:

select * from( 
select  trim(regexp_substr(
replace (
replace (
replace (
REGEXP_SUBSTR('SELECT * FROM DIM_TABLE INNER JOIN FCT_TABLE ON FCT_TABLE.COL1 = DIM_TABLE.COL1 LEFT OUTER JOIN ABC_TABLE WHERE','FROM (.*) WHERE',1,1,null,1),
 'INNER JOIN',',') ,'LEFT OUTER JOIN',','), 'ON',',XXX')
 ,'[^,]+', 1, level) ) value, level
  from dual
 connect by regexp_substr(
   replace (
replace (
replace (
REGEXP_SUBSTR('SELECT * FROM DIM_TABLE INNER JOIN FCT_TABLE ON FCT_TABLE.COL1 = DIM_TABLE.COL1 LEFT OUTER JOIN ABC_TABLE WHERE','FROM (.*) WHERE',1,1,null,1),
 'INNER JOIN',',') ,'LEFT OUTER JOIN',','), 'ON',',XXX')
   , '[^,]+', 1, level) is not null
   )
where 
value like 'DIM%' or value like 'FCT%' or value like 'ABC%'

Results 2

DIM_TABLE   1
FCT_TABLE   2
ABC_TABLE   4

It is not pretty - but it works - I think you got the idea...

Upvotes: 1

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

  SELECT table_name
      FROM user_tables 
      WHERE table_name like 'DIM%'
           OR table_name like 'FCT%'
           OR table_name like 'ABC%'

Upvotes: 1

Related Questions