Regex pattern that matches SQL object (table/function/view)?

Question

Question:

Given a SQL string like

CREATE VIEW TestView AS
SELECT value1, value2 
FROM TABLE_0
UNION
(SELECT * FROM TABLE_2) AS value1
,value2 
FROM TABLE_12 
UNION
SELECT * FROM TABLE_3

(in lowercase)

And an array of string like

string[] tables = new string[]{"table_1", "table_2", "table_3"}

Now I did:

if (strViewDefinition.Contains(strObjectName)) // aaaaargh

for each strObjectName in tables, to check whether or not the view depends on this object.

But this fails on table_12 (and results in cyclic dependencies) for example, because tables contains "table_1". Aaargh.

I need a regex that can check if the view definition contains a function name, table-valued function or another view...

( For checking with System.Text.RegularExpressions.Regex.IsMatch( )

My trial was:

string whateverneedsescaping= System.Text.RegularExpressions.Regex.Escape(@"+-*\/%=,\n\r");
string fieldsep = @"[\s\n\r," + whateverneedsescaping+ "]*";
string strPattern = fieldsep + "VIEW/FUNCTION_NAME" + fieldsep;

But it fails, plus it doesn't account for object names embraced in brackets, like

 "[TABLE_NAME]"

Anybody can help ?

@Mark Bannister: Yes THIS is SQL-Server. But SQL-Server 2005. Plus it is intended for other Db systems as well, so don't recommend me SMO's (buggy on SQL 2005 as well btw) ;-) — Stefan Steiger
– Stefan Steiger, Commented Apr 3, 2012 at 9:02

Robbie · Accepted Answer · 2012-04-03 10:52:15Z

2

I think the reason it doesn't work is because you have the - character in your set. This character is not escaped by Regex.Escape and so your character set ends up containing the following:

[\s\n\r,\+-\*\\/%=,\\n\\r]

The - in this set tries to create a range between the literal characters + and * which doesn't make any sense and hence it throws an ArgumentException with the message [x-y] range in reverse order.

While i'm not 100% sure i understand your exact requirements, i think that the suggestion about using word boundaries from Qtax would work.

All i would add is that you could account for the table names surrounded with [] by changing it to:

\[?\btable_1\b]?

answered Apr 3, 2012 at 10:52

Robbie

19.6k4 gold badges44 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Qtax · Accepted Answer · 2012-04-03 10:33:44Z

1

Would the expression \bname\b suffice? \b is a word boundary.

For example \btable_1\b would not match table_12.

If you have many names you could check for them all together with an expression like \b(?:foo|bar|baz)\b.

answered Apr 3, 2012 at 10:33

Qtax

34k9 gold badges92 silver badges127 bronze badges

1 Comment

Stefan Steiger Over a year ago

It's a bit more complicated than that (word boundary), but thanks for the suggestion of doing it all in one.

Stefan Steiger · Accepted Answer · 2012-04-03 12:08:22Z

0

This does the job:

string strSchema = "dbo";
string strRegexObjectName = System.Text.RegularExpressions.Regex.Escape(strObjectName);
string strPattern = @"[\s\+\-\(,/%=\*\\](\[?" + strSchema + @"\]?\.)?\[?" + strRegexObjectName + @"\]?([\s\+\-\),/%=\*\\]|$)";

edited Apr 3, 2012 at 12:08

answered Apr 3, 2012 at 11:55

Stefan Steiger

83.1k70 gold badges405 silver badges461 bronze badges

Collectives™ on Stack Overflow

Regex pattern that matches SQL object (table/function/view)?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related