Parsing Out Complete Dynamic Sql Expressions From Hundreds Of Stored Procedures

I've inherited a large application that has many hundreds of stored procedures, many of which use dynamic SQL. In order to get a better handle of the types of SQL I am dealing with

Solution 1:

To a first approximation, here's how you'd do it in C# using ScriptDom.

Getting a list of all stored procedure definitions is easy. That can be done in T-SQL, even:

sp_msforeachdb 'select definition from [?].sys.sql_modules'

Or script databases the usual way, or use SMO. In any case, I'm assuming you can get these into a List<string> somehow, for consumption by code.

Microsoft.SqlServer.TransactSql.ScriptDom is available as a NuGet package, so add that to a brand new application. The core of our problem is writing a visitor that will pluck the nodes we're interested in from a T-SQL script:

classDynamicQueryFinder : TSqlFragmentVisitor {
  public List<ScalarExpression> QueryAssignments { get; } = new List<ScalarExpression>();
  publicstring ProcedureName { get; privateset; }

  // Grab "CREATE PROCEDURE ..." nodespublicoverridevoidVisit(CreateProcedureStatement node) {
    ProcedureName = node.ProcedureReference.Name.BaseIdentifier.Value;
  }

  // Grab "SELECT @Query = ..." nodespublicoverridevoidVisit(SelectSetVariable node) {
    if ("@Query".Equals(node.Variable.Name, StringComparison.OrdinalIgnoreCase)) {
      QueryAssignments.Add(node.Expression);
    }
  }

  // Grab "SET @Query = ..." nodespublicoverridevoidVisit(SetVariableStatement node) {
    if ("@Query".Equals(node.Variable.Name, StringComparison.OrdinalIgnoreCase)) {
      QueryAssignments.Add(node.Expression);
    }
  }

  // Grab "DECLARE @Query = ..." nodespublicoverridevoidVisit(DeclareVariableElement node) {
    if (
      "@Query".Equals(node.VariableName.Value, StringComparison.OrdinalIgnoreCase) && 
      node.Value != null
    ) {
      QueryAssignments.Add(node.Value);
    }
  }
}

Let's say procedures is a List<string> that has the stored procedure definitions, then we apply the visitor like so:

foreach (string procedure in procedures) {
  TSqlFragment fragment;
  using (var reader = new StringReader(procedure)) {
    IList<ParseError> parseErrors;
    var parser = new TSql130Parser(true);  // or a lower version, I suppose
    fragment = parser.Parse(reader, out parseErrors);
    if (parseErrors.Any()) {
      // handle errorscontinue;
    }
  }
  var dynamicQueryFinder = new DynamicQueryFinder();
  fragment.Accept(dynamicQueryFinder);
  if (dynamicQueryFinder.QueryAssignments.Any()) {
    Console.WriteLine($"===== {dynamicQueryFinder.ProcedureName} =====");
    foreach (ScalarExpression assignment in dynamicQueryFinder.QueryAssignments) {
      Console.WriteLine(assignment.Script());
    }
  }
}

.Script() is a little convenience method I cobbled up so we can turn fragments back into plain text:

publicstaticclassTSqlFragmentExtensions {
  publicstaticstringScript(this TSqlFragment fragment) {
    return String.Join("", fragment.ScriptTokenStream
      .Skip(fragment.FirstTokenIndex)
      .Take(fragment.LastTokenIndex - fragment.FirstTokenIndex + 1)
      .Select(t => t.Text)
    );
  }
}

This will print all expressions in all stored procedures that are assigned to a variable named @Query.

The nice thing about this approach is that you will have the statements parsed at your fingertips, so more complicated processing, like turning the string expressions back into their unescaped forms or hunting for all instances of EXEC(...) and sp_executesql (regardless of variable names involved), is also possible.

The drawback, of course, is that this isn't pure T-SQL. You can use any .NET language you like for it (I've used C# since I'm most comfortable with that), but it still involves writing external code. More primitive solutions like just CHARINDEXing your way over strings may work, if you know that all code follows a particular pattern that is simple enough for T-SQL string operations to analyze.