Regular expression to find all table names in a query

Question

I am not that hot at regular expressions and it has made my little mind melt some what.

I am trying to find all the tables names in a query. So say I have the query:

SELECT one, two, three FROM table1, table2 WHERE X=Y

I would like to pull out "table1, table2" or "table1" and "table2"

But what if there is no where statement. It could be the end of the file, or there could be a group by or an order by etc. I know "most" of the time this will not be an issue but I don't like the idea of coding for "most" situations and knowing I have left a hole that could cause things to go wrong later.

Is this a doable Regex expression? Am I being a Regex pleb?

(P.S. this will be done in C# but presume that doesn't matter much).

Answer 1

I found this site that has a GREAT parser!

http://www.sqlparser.com/

well worth it. Works a treat.

Answer 2

One workaround is to implement a naming convention on tables and views. Then the SQL statement can be parsed on the naming prefix.

For example:

SELECT tbltable1.one, tbltable1.two, tbltable2.three
FROM tbltable1
    INNER JOIN  tbltable2
        ON tbltable1.one = tbltable2.three

Split whitespace to array:

("SELECT","tbltable1.one,","tbltable1.two,","tbltable2.three","FROM","tbltable1","INNER","JOIN","tbltable2","ON","tbltable1.one","=","tbltable2.three")

Get left of elements to period:

("SELECT","tbltable1","tbltable1","tbltable2","FROM","tbltable1","INNER","JOIN","tbltable2","ON","tbltable1","=","tbltable2")

Remove elements with symbols:

("SELECT","tbltable1","tbltable1","tbltable2","FROM","tbltable1","INNER","JOIN","tbltable2","ON","tbltable1","tbltable2")

Reduce to unique values:

("SELECT","tbltable1","tbltable2","FROM","INNER","JOIN","ON")

Filter on Left 3 characters = "tbl"

("tbltable1","tbltable2")