Parsing SQL script for table names

Question

I would like to parse SQL INSERT statements into object (represented as a text). There is an SQL script file containing:

INSERT INTO Document(Id, Name, Description ...)
  VALUES('DC001', 'FOO', 'bar'); 

INSERT INTO DocType(Id, Name)
  VALUES('DT001', 'DOCX');

and many more table inserts.

What is the easiest way to parse out Table names (Document, DocType,..)?

Would it be possible with RegEx, if I don't wanna calculate substrings?

const string pattern = @"INSERT INTO\s\w";

        foreach (var line in FileContent)
        {
            var a = Regex.Match(line, pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
            if (a.Success)
            {

            }
        }

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

Instead of using Regex and probably missing out lots of edge cases you have not considered, look at using a dedicated SQL Parser.

There are several related questions for SQL parser for .NET.

edited May 23 '17 at 11:59

Community

1
1

answered Mar 07 '12 at 17:04

Oded

489,969
99
883
1,009

This is all it is, no edge cases. All I need is Table names, Fields and Values in arrays. – kayz1 Mar 07 '12 at 17:16
@kayz1 - I am talking about formatting edge cases, different ways of writing the required DML etc. – Oded Mar 07 '12 at 19:33
I agree with Oded, Regex is the poor man solution, it can and will break eventually, and down the road cause more costs than profit. – Agustin Garzon Feb 24 '16 at 12:47

score 0 · Accepted Answer · answered Mar 09 '13 at 17:46

Regex did the trick

private readonly IList<string> _tableList = new List<string>();

public const string TableName = @"\s*(INSERT|UPDATE)\s*(INTO|\w+)\s*(\w+)\s*(\(|VALUES|SET)";
public static readonly Regex ValidLine = new Regex(TableName, RegexOptions.Compiled | RegexOptions.IgnoreCase);

var currentTableName = ValidLine.Match(line);
var value = currentTableName.Groups[3].Value;

if (!_tableList.Contains(value))
{
        _tableList.Add(value);
}

score -1 · Answer 3 · answered Sep 07 '18 at 00:36

-1

I had similar problem, parsing SQL files and come up with this universal Parser on Ruby. It covered all cases and log scan result into file for further review. Can be adjusted for other types too. Take R10.rb file from here

answered Sep 07 '18 at 00:36

Mike S

296
2
14

Parsing SQL script for table names

3 Answers3