0

I'm trying to use visual studio code to search our code base for SQL statements table declarations are missing the schema using a regex expression.

So far I've got this

(from|inner join|left join)((\s|\r|\r\n)*)(dbo|sys)

which selects where the schema is included, but I'm having trouble excluding where the schema (or table name) is not dbo or sys e.g. "Select * from company" should be found, where "Select * from dbo.company" should not.

I though it would be

(from|inner join|left join)((\s|\r|\r\n)*)(!dbo|!sys)

but that selects nothing.

and I'm aware this misses APPLYs, INSERTs, etc.

starball
  • 20,030
  • 7
  • 43
  • 238
sfcnzl
  • 13
  • 4
  • 1
    Are you searching through application code for ad-hoc sql or within sql objects like procedures, view and functions? What about where optional `inner` clause is excluded, `outer` is included, `full` joins etc and multiple spaces or other allowable characters exist? – Stu Mar 02 '23 at 21:58
  • Related: [Regex lookahead, lookbehind and atomic groups](/q/2973436/11107541). Can you add an example of some texts that you would want selected and some that you wouldn't want selected? – starball Mar 02 '23 at 22:00
  • 1
    why not just search for JOIN... Also, don't forget APPLY – siggemannen Mar 02 '23 at 22:03

2 Answers2

0

You should be able to do it using negative lookahead (see Regex lookahead, lookbehind and atomic groups):

(from|inner join|left join)((\s|\r|\r\n)*)(?!(dbo|sys)\.)\w+

The "\w" above just matches word characters ([a-zA-Z0-9_]), but you can replace it with a more suitable regex. Add quotes or other characters as needed if you also want to match quoted names (use ? for optional match- like "?)

starball
  • 20,030
  • 7
  • 43
  • 238
0

Something like this seem to work quite alright in my proc collection:

(\s|\r|\r\n)+(from|join|apply)(\s|\r|\r\n)+\w+(\s|\r|\r\n)+(\w+|\()

I'm basically skipping the lookahead since it never works, instead i try to match everything that doesn't contain two part name ie: from somevalue (alias or index hint)

It won't match quoted names, but it's probably easy enough to change \w+ to a quoted version of it

siggemannen
  • 3,884
  • 2
  • 6
  • 24