Grab Tablename From Stored SELECT Statement Using Oracle’s REGEXP Functions

I have an sqltext column in my database that contains select statements like

  • select firstname, lastname from employees

  • select firstname, lastname from managers
    where empid = 21

I need to extract the tablenames from these statements. Here is my cheeseball solution:

select
trim(REGEXP_REPLACE(replace(sqltext,chr(10),' '),
'(.*)( from )(.*)( where .*)' ||case when instr(replace(sqltext,chr(10), ' '), ' where ') = 0 then '?' end,
'-\3-',1,0,'i'))
from someTableThatContainsSqlText

Published in: on December 9, 2010 at 1:18 pm  Leave a Comment  
Tags: , ,