0

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

StackThis
  • 1,262
  • 1
  • 14
  • 23
  • What are you trying to accomplish or get out of parsing these statements? – Ryan Gates Feb 15 '13 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 Feb 15 '13 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 Feb 15 '13 at 22:16
  • It is only DDL. i.e schemas only no data. And yes it is for MS SQL 2008 – StackThis Feb 15 '13 at 22:21
  • 1
    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 (http://www.red-gate.com/) – Daniel James Bryars Feb 15 '13 at 22:34
  • We have redgate too. But Redgate is entirely for a different purpose. – StackThis Dec 11 '14 at 20:52

2 Answers2

3

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

Yahia
  • 69,653
  • 9
  • 115
  • 144
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.

StackThis
  • 1,262
  • 1
  • 14
  • 23
  • How about if the the schema and object name are not qualified with brackets? – db_brad Aug 26 '15 at 21:40
  • 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. – RickC Sep 18 '15 at 13:52
  • Downvoted for describing your own (very incomplete) SQL "parser" as "awesome[ness]" – El Ronnoco Sep 12 '17 at 13:00