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

Question

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 :

CREATE PROC / CREATE PROCEDURE
ALTER PROC / ALTER PROCEDURE
DROP PROC / DROP PROCEDURE
CREATE TABLE/TRIGGER/VIEW
ALTER TABLE/TRIGGER/VIEW
DROP TABLE/TRIGGER/VIEW
etc

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

What are you trying to accomplish or get out of parsing these statements? — Ryan Gates
– Ryan Gates, Commented Feb 15, 2013 at 21:48
We are creating an in-house tool to launch .SQL procs to production without having the DBA to manually backup the affected objects, doing the launch and if something goes wrong, rolling it back manually. So, this tool will be given a .SQL file, it will parse all the object names, take their backup and then execute the .sql — StackThis
– StackThis, Commented Feb 15, 2013 at 21:49
When you say backup, do you mean the schema/definition of the objects? Or do you mean the data as well. Is this for MS SQL? If so, what version? — Ryan Gates
– Ryan Gates, Commented Feb 15, 2013 at 22:16
It is only DDL. i.e schemas only no data. And yes it is for MS SQL 2008 — StackThis
– StackThis, Commented Feb 15, 2013 at 22:21
We use Red Gate's SQL Compare to do manage this kind of thing, it's commercial but we've found it good value for money (red-gate.com) — Daniel James Bryars
– Daniel James Bryars, Commented Feb 15, 2013 at 22:34

Yahia · Accepted Answer · 2013-02-15 21:49:03Z

3

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

answered Feb 15, 2013 at 21:49

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

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

Comments

StackThis · Accepted Answer · 2013-02-15 23:30:08Z

1

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.

answered Feb 15, 2013 at 23:30

StackThis

1,2521 gold badge15 silver badges23 bronze badges

3 Comments

db_brad Over a year ago

How about if the the schema and object name are not qualified with brackets?

RickC Over a year ago

Agreed, @db_brad. Also... * What if the object names contain one more embdded bracket or '.' characters? * What if the schema name is not dbo? Parsing SQL object names is tough. T-SQL provides parsename() and quotename() which make the process (relatively) simple in a T-SQL script. However, I've been unable to find similar functions available to the .NET languages -- C#, VB, etc. -- in System.Data.Sql*, in SMO, on the 'Net... nowhere.

El Ronnoco Over a year ago

Downvoted for describing your own (very incomplete) SQL "parser" as "awesome[ness]"

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related