sqlregexregex-greedy

Extract tables from a stored procedure

I'm trying to extract a list of tables in FROM|JOIN with a regex but I want to exclude all aliases and spaces in order to get only the schema and table (SCHEMA.TABLE).

The following regex do it but I can't exclude the aliases:

(?i)(?:FROM|JOIN) .*?(.*\..*)( ?\w)$

SELECT A,B FROM SCHEMA.TABLE TB WHERE A=B; --> Return 'SCHEMA.TABLE TB', I want to exclude everything after the table name.

Any help is appreciated.

EDIT: Some tables don't have alias at the end of the line:

Case 1:

SELECT A,B
FROM SCHEMA.TABLE TB
WHERE A=B

Case 2:

SELECT A,B
FROM SCHEMA.TABLE
WHERE A=B

So the REGEX should work in both cases.

Solution

  • I guess I found the regex that works:

    (?i)(?:FROM|JOIN) .?(..[^ ]\w*)

    What is important is the use at the end of \w*, that is selecting only word characters.

    Hope this helps!