SQL-Parser for multiple SQL commands

asked 2022-12-23T12:56:56.197+00:00
Jan Weerts 21 Reputation points

Hi,
i need a SQL-Parser that can split a SQL String into its components.

In the example, the SQL-Parser should detect that there a 2 separate SQL commands.

Does anyone know a SQL-Parser that can do that.

The SQL parser should be in C#, .NET or a MS SQL Server function.

Example:
273743-image.png

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
6,911 questions
No comments
{count} votes

Accepted answer
  1. answered 2022-12-24T14:16:40.27+00:00
    Dan Guzman 6,926 Reputation points

    I've found the Microsoft.SqlServer.TransactSql.ScriptDom parser @PatriceSc mentioned to be very useful for T-SQL code analysis. Below is an sample .NET 6 console application that uses a visitor to find T-SQL statements in the script. This references the latest NuGet package.

    using Microsoft.SqlServer.TransactSql.ScriptDom;  
      
    SqlParser.Parse(@"  
    SELECT * FROM angebot  
    UPDATE angebot SET kunde = 13 WHERE kunde = 10033  
    ");  
      
    public class SqlParser : TSqlFragmentVisitor  
    {  
      
        public override void Visit(TSqlStatement fragment)  
        {  
            Console.WriteLine($"Found statement {fragment.GetType().Name} at line {fragment.StartLine}, column {fragment.StartColumn}, length {fragment.FragmentLength}");  
        }  
      
      
        public static void Parse(string sqlScript)  
        {  
      
            var parser = new TSql160Parser(true);  
            IList<ParseError> parseErrors = new List<ParseError>();  
            var stringReader = new StringReader(sqlScript);  
      
            var fragment = parser.Parse(stringReader, out parseErrors);  
            if (parseErrors.Count > 0) {  
                throw new InvalidDataException($"{parseErrors.Count} parsing error(s)");  
            }  
      
            var visitor = new SqlParser();  
      
            fragment.Accept(visitor);  
      
        }  
      
    }  
    

    Output:

    Found statement SelectStatement at line 2, column 1, length 21  
    Found statement UpdateStatement at line 3, column 1, length 49  
    
    No comments

1 additional answer

Sort by: Most helpful
  1. answered 2022-12-23T13:50:26.727+00:00
    PatriceSc 161 Reputation points

    Hi,

    Unclear which kind of accuracy you need. At worst seems you have parsers available at https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsqlparser?source=recommendations&view=sql-dacfx-160 likely as part of https://www.nuget.org/packages/Microsoft.SqlServer.DacFx (never used them).

    Or maybe something simpler depending on what you are trying to do exactly and which constraints you can accept (ie just checking for few keywords that are starting a statement ?)

    No comments