0845 643 64 63

Regular Expression to get tables from SQL query

If you’ve not come across regular expressions (RegEx) before then you’re missing out, they’re an incredibly powerful tool for matching, extracting or validating patterns of text.

I had a use case this week where I needed to take a SQL query, and quickly identify every table or view that was used in the query.

RegEx to the rescue!

(?<=((FROM[ \n\r]+)|(JOIN[ \n\r]+)|(APPLY[ \n\r]+)))((.?([a-zA-Z0-9_]+|([[a-zA-Z0-9._[]\s\$(){}+\?\<>|!]+])|("[a-zA-Z0-9._[]\s\$(){}+\?\<>|!]+")))+)

Let me break this down into chunks:

  1. We want any text that occurs after any of the following: FROM, JOIN, APPLY. We can use a ‘lookbehind’ assertion for this, and each of these words can be followed by any number of spaces, carriage returns or line feeds.
(?<=((FROM[ \n\r]+)|(JOIN[ \n\r]+)|(APPLY[ \n\r]+)))
  1. Then we want to have any number of repetitions of [an optional full stop, followed by] an entity name (entity being database, schema, table, view). The entity name being any combination of lower case, upper case, digits or underscore
(.?[a-zA-Z0-9_]+)
  1. We then extend this to say if the entity is surrounded by [ ] or ” ” then other reserved characters are allowed as well, most of these have to be escaped in RegEx using \

([[a-zA-Z0-9._[]\s\$(){}+\?\<>|!]+])

(“[a-zA-Z0-9._[]\s\$(){}+\?\<>|!]+”)

We can then put them all together to give us a list of tables. To test this I love Derek Slager’s awesome online RegEx test utility – makes testing any RegEx simple and quick.

A big shout out to @RolandBouman, @MobileCK and @TheStephLocke for pointing out some gaps and optimisations.

That’s all for now, </FrogBlog Out>

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

367,745 Spambots Blocked by Simple Comments

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon