Question 134327 in Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – How can I write a regex search for SQL statements missing a table schema in VS Code?

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|rn)*)(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|rn)*)(!dbo|!sys)

but that selects nothing.

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

Tags:

2

Answers


  1. 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|rn)*)(?!(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 "?)

    Login or Signup to reply.
  2. 0

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

    (s|r|rn)+(from|join|apply)(s|r|rn)+w+(s|r|rn)+(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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search