Разбор полных динамических выражений SQL из сотен хранимых процедур

Question

Я унаследовал большое приложение с сотнями хранимых процедур, многие из которых используют динамический SQL. Чтобы лучше понять типы SQL, с которыми я имею дело, было бы чрезвычайно полезно, если бы у меня был способ проанализировать текст запроса всех этих хранимых процедур и извлечь полное выражение для любого динамического SQL, содержащегося в нем.

Упрощенное выражение может быть:

declare @query nvarchar(max)
set @query = 'SELECT col1,col2,col3 from ' + @DatabaseName + '.dbo.' + @TableName + ' WHERE {some criteria expression that also contains inline quotes}'

Вывод, который я ищу для вышеупомянутого (который в конечном итоге будет вызван в одном запросе, который анализирует все хранимые процедуры):

SELECT col1, col2, col3 
FROM ' + @DatabaseName + '.dbo.' + @TableName + ' 
WHERE {some criteria expression that also contains inline quotes}

Таким образом, не выражение после того, как были переданы значения параметров, а текст выражения, как в тексте хранимой процедуры, включая имена параметров.

Я согласен с небезопасным предположением, что имя динамического параметра SQL @queryпоэтому поиск этого в выражении SQL для использования в качестве начальной позиции для извлечения текста будет допустимым, но, поскольку в строке есть одинарные кавычки, у меня нет простого способа узнать, где завершено присвоение переменной.

Я включаю теги [antlr] и [parsing] в этот вопрос, потому что у меня такое ощущение, что это выходит за рамки возможностей T-SQL.

PS: Да, я хорошо знаю, "Я не должен был делать это".

РЕДАКТИРОВАТЬ

Исходя из предложения ниже, попробовал следующий запрос, но не очень полезный в этом контексте:

SELECT 
db_name(dbid) DB_NAME
,cacheobjtype, objtype, object_name(objectid) ObjectName
,objectid 
,x.text
,usecounts 
--  , x.*,z.* ,db_name(dbid)
FROM 
sys.dm_exec_cached_plans z
CROSS APPLY sys.dm_exec_sql_text(plan_handle)  x
WHERE 
    --usecounts > 1 
    --objType = 'Proc' and  -- include if you only want to see stored procedures 
    db_name(dbid) not like 'ReportServer%' and db_name(dbid) <> 'msdb' and db_name(dbid) not like 'DBADB%' and db_name(dbid) <> 'master'
--ORDER BY usecounts DESC
ORDER BY objtype

Answer 1

В первом приближении, вот как бы вы сделали это в C#, используя ScriptDom,

Получить список всех определений хранимых процедур очень просто. Это можно сделать в T-SQL, даже:

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

Или скрипт базы данных обычным способом, или используйте SMO. В любом случае, я предполагаю, что вы можете получить их в List<string> как-то для потребления по коду.

Microsoft.SqlServer.TransactSql.ScriptDom доступен в виде пакета NuGet, так что добавьте его в новое приложение. Суть нашей проблемы - написание посетителя, который извлечет интересующие нас узлы из сценария T-SQL:

class DynamicQueryFinder : TSqlFragmentVisitor {
  public List<ScalarExpression> QueryAssignments { get; } = new List<ScalarExpression>();
  public string ProcedureName { get; private set; }

  // Grab "CREATE PROCEDURE ..." nodes
  public override void Visit(CreateProcedureStatement node) {
    ProcedureName = node.ProcedureReference.Name.BaseIdentifier.Value;
  }

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

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

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

Скажем procedures это List<string> у которого есть определения хранимых процедур, тогда мы применяем посетителя следующим образом:

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 errors
      continue;
    }
  }
  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() Это небольшой удобный метод, который я использовал, чтобы мы могли превратить фрагменты в простой текст:

public static class TSqlFragmentExtensions {
  public static string Script(this TSqlFragment fragment) {
    return String.Join("", fragment.ScriptTokenStream
      .Skip(fragment.FirstTokenIndex)
      .Take(fragment.LastTokenIndex - fragment.FirstTokenIndex + 1)
      .Select(t => t.Text)
    );
  }
}

Это напечатает все выражения во всех хранимых процедурах, которые назначены переменной с именем @Query,

Хорошая вещь в этом подходе состоит в том, что вы будете иметь синтаксический анализ операторов у вас под рукой, что делает более сложную обработку, например, превращение строковых выражений обратно в их формы без экранирования или поиск всех экземпляров EXEC(...) а также sp_executesql (независимо от имен переменных), также возможно.

Недостатком, конечно, является то, что это не чистый T-SQL. Вы можете использовать любой язык.NET, который вам нравится (я использовал C#, так как мне удобнее всего), но он все еще включает в себя написание внешнего кода. Более примитивные решения, такие как просто CHARINDEX Если вы знаете, что весь код следует определенному шаблону, достаточно простому для анализа строковых операций T-SQL, то это может сработать.