CODEWITHSUNDEEP
c#regexparsingsql-parsertsql-parser
StackThis
StackThis

Reputation: 1252

RegEx to parse stored procedure and object names from DDL in a .sql file C#

I have a .sql file which may contain DDL definitions of several stored procedures, alter statements for Tables, Triggers, Views etc

It could have statements like these :

What is the best way to parse the .sql file and just get the list of objects(Proc/Table/View names) and the action that is being taken on them (ALTER/CREATE/DROP)? I am thinking instead of using stuff like Microsoft.Data.Schema.ScriptDom or ANTLR or other parsers, the easiest way is to use a RegEx. But not sure what kind of RegEx should I write which covers all the scenarios.

So far, this expression is able to match all the above rules. But how do I get the name of the object in front of it. eg. it matches

(create|alter|drop)\s+(procedure|proc|table|trigger|view|function|constraint)

How do I get the name AdvisorGroups is my question. My RegEx is also imperfect because it could have [dbo]. in front of it or not. It could just be Alter table AdvisorGroups too. I am not trying to take care of all possibilites. Just the bare minimum.

ALTER TABLE [dbo].[AdvisorGroups] ADD CONSTRAINT [XXX]

-Thanks in advance

Upvotes: 0

Views: 4519

Answers (2)

StackThis
StackThis

Reputation: 1252

I worked it out myself. Hope it helps someone. So for a string containing a jumble of ALTER, DROP, CREATE PROC/TABLE/TRIGGER/FUNCTION

    Regex DDL = new Regex(@"(?<=\b(create|alter|drop)\s+(procedure|proc|table|trigger|view|function)\b\s\[dbo\].)\[.*?\]", RegexOptions.IgnoreCase);
    Match match = DDL.Match(inputScript);
    while(match.Success)
    {
        textBox2.Text += "\r\n" + match.Groups[1].Value + " - " + match.Groups[2].Value + " - " +  match.Value;
        match = match.NextMatch();
    }
    textBox3.Text = "DONE";

You will get output like this.

  • CREATE - VIEW - vwEmployees
  • CREATE - TABLE - tblSalaries
  • DROP - PROC - spGetEmployees
  • ALTER - TABLE - tblSalaries

The awesomeness of this code is that, not only does it give you the object being affected but it even gives you the kind of object (i.e table, proc, view) and what operation (eg. create/alter/drop). All these are available in matches[1], matches[2] etc.

Upvotes: 1

Yahia
Yahia

Reputation: 70379

RegEx won't get the job done - definitely... you will need a real parser - like this one (commercial).

Upvotes: 3

Related Questions