Эта статья о библиотеке Microsoft.SqlServer.TransactSql.ScriptDom и опыте её использования для тех, кто работает с T-SQL — разработчиков и администраторов. Если нужно анализировать зависимости в коде, менять структуру, разбирать сложные запросы или генерировать код, то эта библиотека — то, что нужно.

Введение

Допустим, у нас есть проект на T-SQL с многолетней историей, с сотнями таблиц и тысячами процедур и функций. За эти годы сменилось несколько составов разработчиков, и даже старожилы проекта уже не могут упомнить все детали и нюансы.  Проект растет, старое переписывается, новое добавляется. Кодовая база разрастается до десятков мегабайт, и для того, чтобы эффективно с ней работать, стандартных инструментов уже не хватает. Разборы проблем, уточнение нюансов бизнес-логики, переписывание функционала занимают много времени.

Например, нам нужно узнать, какие процедуры вставляют или изменяют записи в конкретной таблице. Простой текстовый поиск неэффективен — таблица может использоваться в десятках мест, ее название может оказаться в закомментированном блоке кода. Приходится самому просматривать каждый найденный фрагмент, чтобы убедиться, что таблица действительно изменяется. Регулярные выражения тоже не спасают — синтаксис T-SQL допускает множество вариантов написания SQL.  Можно попробовать поискать, используя возможности самого SQL Server — системное представление sys.sql_expression_dependencies  и табличную функцию sys.dm_sql_referenced_entities, — но и они не дадут полной картины.

Нужен полноценный парсер кода.

Парсер разбирает входной текст на компоненты в соответствии с правилами, описанными в синтаксической нотации языка, и строит из этих компонентов иерархию. Такая иерархия называется AST (Abstract Syntax Tree) — абстрактное синтаксическое дерево. Каждый элемент этого дерева является  типизированным объектом. Такой подход превращает разбор кода в обычную работу с формализованными объектами и их полями. Анализируя эти объекты, их поля и иерархию, можно автоматизировать массу задач по обработке кода.

Например, для нашей задачи можно распарсить весь T-SQL код проекта с известной детализацией и сохранить результат в графовой БД. В этом случае анализ зависимостей процедур и таблиц становится очень быстрым. Фактически, можно писать запросы к коду как к обычной базе данных. Граф зависимостей можно визуализировать, найти в нем неиспользуемый код, использовать как базу знаний для нейросетей (RAG).  Словом, новых возможностей открывается очень много.

Такой парсер можно легко написать с помощью Microsoft.SqlServer.TransactSql.ScriptDom (далее ScriptDom). Данная библиотека написана на .NET, используется в SQL Server Management Studio и SQL Server Data Tools.  Помимо парсинга, библиотека позволяет валидировать, генерировать, анализировать и форматировать код. Код библиотеки частично открыт.

Как отмечалось ранее, парсер генерирует дерево объектов, которые являются элементами языка T-SQL. На текущий момент библиотека содержит более тысячи типов узлов (классов), представляющих элементы языка. Библиотека предоставляет мощный инструментарий для работы с исходным кодом, но что именно и как нужно вытащить из исходников – остается за разработчиком.

Основные классы и методы

Вся обработка сложных правил T-SQL происходит внутри библиотеки. Результат — абстрактное синтаксическое дерево, самая сложная работа выполнена внутри библиотеки. Далее нужно только обойти дерево, анализируя и сохраняя нужную информацию. Такой обход в ScriptDom реализован с помощью паттерна Посетитель. Для обработки нужных типов узлов, например, выражения для имени таблицы, нужно переопределить соответствующий виртуальный метод и добавить в него нужную логику.

Основные классы библиотеки:

TSqlParser: Базовый класс для разбора кода. Существуют версии для разных редакций, например, TSql150Parser для SQL Server 2019, TSql160Parser - SQL Server 2022.

TSqlFragment: Базовый класс для любого элемента кода. Упомянутая ранее тысяча классов элементов языка T-SQL наследуется от этого класса — от литерала до процедуры: Классы-потомки TSqlFragment (ссылка на большую картинку)

TSqlFragmentVisitor и TSqlConcreteFragmentVisitor: Классы для обхода дерева и выполнения нужных действий при нахождении определенных элементов. 

SqlScriptGenerator: Инструмент для обратной сборки объектов в текстовый SQL-файл.

Рассмотрим подробнее TSqlFragmentVisitor. Этот класс реализует обход дерева. Его ключевые методы — Visit и ExplicitVisit. Класс содержит сотни перегрузок этих методов для обхода всех возможных типов узлов T-SQL. 

Эти методы вызываются при обходе иерархии узлов. Клиентское приложение вызывает метод Accept, передавая в качестве аргумента экземпляр TSQLFragmentVisitor. Далее вызывается метод ExplicitVisit, внутри себя он вызывает Visit для посещения своих дочерних узлов:

ExplicitVisit в TSqlFragmentVisitor посещает все типы узлов по иерархии класса узла, вызывая поочередно Visit для каждого родительского класса узла и затем для него самого:

public virtual void ExplicitVisit(NamedTableReference node)
{
    if (VisitBaseType) //В TSqlFragmentVisitor это поле имеет значение true
     {
        Visit((TableReferenceWithAlias)node);
        Visit((TableReference)node);
        Visit((TSqlFragment)node);
    }
    Visit(node);
    node.AcceptChildren(this);
}

Также в ExplicitVisit вызывается метод node.AcceptChildren() для обработки дочерних узлов. В этом методе каждый дочерний узел инициализирует свой обход с помощью метода Accept и тот же процесс разбора повторяется уже для него. Поэтому, чтобы не потерять  обработку дочерних узлов, в переопределенных ExplicitVisit нужно обязательно вызывать base.ExplicitVisit(node) или node.AcceptChildren.

А вот базовая реализация метода Visit:

public virtual void Visit(NamedTableReference node)
{
    if (!VisitBaseType) //В TSqlFragmentVisitor это поле имеет значение true
     {
        Visit((TSqlFragment) node);
     }
}

Так как внутреннее свойство VisitBaseType имеет значение true, дополнительный Visit не вызывается, поэтому в переопределенных Visit вызывать базовый метод не нужно.

Второй класс для обхода дерева — TSQLConcreteFragmentVisitor, дочерний класс TSQLFragmentVisitor. TSqlConcreteFragmentVisitor запрещает переопределять методы Visit и ExplicitVisit с параметрами абстрактных типов, например JoinTableRefernce. Единственное исключение — метод Visit(TSqlFragment node), для общего предка всех типов узлов.

В TSqlConcreteFragmentVisitor значение внутреннего свойства VisitBaseType равно false, следовательно, изменяется логика посещения. Теперь ExplicitVisit пропускает посещение узлов с базовыми типами и просто вызывает Visit, который, в свою очередь, вызывает посещение для самого базового узла иерархии — единственный незапечатанный метод с абстрактным типом параметров. Поэтому в наследниках TSqlConcreteFragmentVisitor при переопределении метода Visit нужно вызывать его базовую реализацию.

В ScriptDom есть особенности с обработкой иерархии узлов. В отличие от ANTLR или Roslyn, узел не содержит ссылку на родительский узел. Для получения иерархии узлов нужно явно добавлять узел в стек перед обработкой дочерних узлов и удалять из стека, когда обработка завершена. Для этого нужно переопределить ExplicitVisit для нужных типов узлов и перед вызовом base.ExplicitVisit добавлять узел в стек, а после base.ExplicitVisit — извлекать.

Пример приложения

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

Создадим консольное приложение и добавим NuGet-пакет Microsoft.SqlServer.TransactSql.ScriptDom.

Добавим в приложение файл example1.sql со следующим содержимым:

create procedure UpdateRequest @Name varchar(100)
as
declare
	@PersonId int
begin
	select @PersonId = @Id
	from Person
	where Name = @Name

	update Request
	set someValue = 100
	where PersonId = @PersonId
end

Если проект создается в Visual Studio, то в свойствах файла можно указать Copy to Output Directory: Copy always, либо явно указать путь к разбираемому файлу в коде ниже.

Теперь напишем код для Program.cs:

using Microsoft.SqlServer.TransactSql.ScriptDom;

var sql = File.ReadAllText("example1.sql"); //Либо полный путь к файлу
using var reader = new StringReader(sql);
var parser = new TSql150Parser(true);
var fragment = parser.Parse(reader, out var errors);
if (errors.Count > 0)
{
	var errorMessages = string.Join(Environment.NewLine,
		errors.Select(e => $"Line {e.Line}, Col {e.Column}: {e.Message}"));

	throw new Exception($"Ошибка синтаксиса T-SQL: {errorMessages}");
}

Сейчас код парсит файл в синтаксическое дерево, но не обходит его. Если в файле нет ошибок, то приложение просто закончит работу. В качестве эксперимента можно попробовать добавить в файл синтаксическую ошибку T-SQL — и парсер сообщит о ней. Ошибка должна быть именно синтаксической, например, selekt вместо select. Парсер не подключается к реальной базе данных и ничего не знает о реальном существовании таблиц и процедур, он просто анализирует синтаксис.

Создадим класс, который будет искать DML-выражения в скрипте.  Нам нужно выбрать базовый класс из двух вариантов - TSQLFragment и TSQLConcreteFragment. В нашем случае удобнее будет TSQLConcreteFragment.

Начнем с Update. Для этого нужно переопределить метод, который отвечает за посещение выражения Update. Мы не будем переопределять логику обхода узлов, поэтому подойдет Visit.

Добавим перегрузку Visit для UpdateStatement :

public class DMLVisitor : TSqlConcreteFragmentVisitor
{
	public override void Visit(UpdateStatement node)
	{   
		Console.WriteLine("-------Update");
	}
}

И добавим в Program.cs создание и вызов нашего посетителя:

using Microsoft.SqlServer.TransactSql.ScriptDom;

var sql = File.ReadAllText("example1.sql");
using var reader = new StringReader(sql);
var parser = new TSql150Parser(true);
var fragment = parser.Parse(reader, out var errors);
if (errors.Count > 0)
{
	var errorMessages = string.Join(Environment.NewLine,
		errors.Select(e => $"Line {e.Line}, Col {e.Column}: {e.Message}"));

	throw new Exception($"Ошибка синтаксиса T-SQL: {errorMessages}");
}
//Поиск DML
var dmlVisitor = new DMLVisitor();
fragment.Accept(dmlVisitor);

Теперь при запуске приложения в консоли выведется строка "-------Update"

Выведем текст для фрагмента. Для этого создадим вспомогательный метод:

public static class Helper
{
	public static string GetNodeText(this TSqlFragment node)
	{
		if (node.ScriptTokenStream == null || node.FirstTokenIndex < 0)
			return "N/A";

		var sb = new StringBuilder();
		for (int i = node.FirstTokenIndex; i <= node.LastTokenIndex; i++)
		{
			sb.Append(node.ScriptTokenStream[i].Text);
		}
		return sb.ToString();
	}
}

Теперь мы можем получать содержимое фрагментов:

Console.WriteLine(node.GetNodeText());

Прочитаем обновляемую таблицу, обратившись к полю node.UpdateSpecification.Target:

public override void Visit(UpdateStatement node)
{
	Console.WriteLine("-------Update");
	Console.WriteLine(node.GetNodeText());
	Console.WriteLine($"Изменяемая таблица: {node.UpdateSpecification.Target.GetNodeText()}");
}

Теперь метод выведет и само выражение Update, и изменяемую в нем таблицу.

Однако, это самый простой случай синтаксиса. Бывает, что update пишут с указанием таблицы в предложении from. Добавим в файл example1.sql этот вариант:

update t
set someValue = null
from Request t 
join Person p on p.Id = t.PersonId
where t.PersonId = @PersonId

В этом случае наш метод выведет всего лишь алиас t, но не саму таблицу. Очевидно, нужно анализировать предложение from, чтобы найти таблицы и их алиасы и по алиасу понять, какая таблица фактически обновляется. В документации по UpdateSpecification указано, что свойство FromClause, в свою очередь, имеет свойство TableReferences типа IList<TableReference> — абстрактный тип, имеющий множество наследников. В конце статьи приведена сводная таблица классов-наследников TableReference и примеры типов фрагментов, который они обрабатывают.

В нашем примере значение FromClause — список с единственным элементом типа QualifiedJoin. QualifiedJoin служит для описания ANSI-джойнов и имеет два поля — FirstTableReference и SecondTableReference. Эти поля тоже могут содержать ссылки на джойны, а значит, мы имеем дело с иерархической структурой неизвестной глубины. Нужен метод, который будет просматривать список TableReference с учетом возможных джойнов.

Сначала добавим в класс Helper вспомогательный метод   для извлечения алиаса:

public static string? GetAlias(this TableReference tr)
{
	if (tr is TableReferenceWithAlias trWithAlias)
		return trWithAlias.Alias?.Value;
	return null;
}

А в класс DMLVisitor добавим методы для вычисления таблицы по алиасу:

public static TSqlFragment ResolveTargetReference(UpdateSpecification specification)
{
	//Если предложения From нет, то возвращаем Target
	if (specification.FromClause == null)
		return specification.Target;
	
	var targetAlias = specification.Target.GetNodeText();
	var resolvedTable = FindTableReferenceByAlias(specification.FromClause.TableReferences, targetAlias);

	return resolvedTable == null ? specification.Target : resolvedTable.SchemaObject;
}

public static NamedTableReference? FindTableReferenceByAlias(IEnumerable<TableReference> sources, string alias)
{
	if (string.IsNullOrEmpty(alias) || !sources.Any())
		return null;

	var queue = new Queue<TableReference>(sources);

	while (queue.Count > 0)
	{
		var currentTableReference = queue.Dequeue();
		if (currentTableReference is QualifiedJoin joinTableReference)
		{
			queue.Enqueue(joinTableReference.FirstTableReference);
			queue.Enqueue(joinTableReference.SecondTableReference);
			continue;
		}
		
		var currentAlias = currentTableReference.GetAlias();

		//T-SQL регистронезависим, поэтому и алиасы надо сравнивать без учета регистра
		if (string.Equals(currentAlias, alias, StringComparison.OrdinalIgnoreCase))
			return currentTableReference as NamedTableReference;
	}
	return null;
}

Метод ResolveTargetReference ищет предложение from и если ее нет, то просто возвращает свойство Target.

Если же предложение from есть, то считаем, что в Target у нас алиас и будем искать для него таблицу в from

В методе FindTableReferenceByAlias из предложения from создается очередь. Если активный элемент этой очереди выражение QualifiedJoin, то добавляем в очередь его поля. Иначе, если есть алиас, сравниваем его с искомым без учета регистра, и если он совпадает, то таблица найдена. Если же не удалось найти таблицу по алиасу, то  в Target уже указана таблица. 

Теперь наш метод корректно возвращает обновляемую таблицу. Добавим в DMLVisitor методы для поиска выражений Insert и Delete.

Для Insert все очень просто:

public override void Visit(InsertStatement node)
{
	Console.WriteLine("-------Insert");
	Console.WriteLine(node.GetNodeText());
	Console.WriteLine($"Изменяемая таблица: {node.InsertSpecification.Target.GetNodeText()}");
}

Для Delete допустимы два варианта синтаксиса — с использованием from и алиаса и напрямую из таблицы. Это очень похоже на вариант с Update. И в самом деле, класс DeleteSpecification, и класс UpdateSpecification наследуются от UpdateDeleteSpecificationBase, который как раз содержит поля FromClause и Target.  Изменим тип параметра specification в методе ResolveTargetReference:

public static TSqlFragment ResolveTargetReference(UpdateDeleteSpecificationBase specification)

Теперь этот метод работает и для Delete:

public override void Visit(DeleteStatement node)
{
	Console.WriteLine("-------Delete");
	Console.WriteLine(node.GetNodeText());
	Console.WriteLine($"Изменяемая таблица: {ResolveTargetReference(node.DeleteSpecification).GetNodeText()}");
}

Теперь наш парсер понимает два основных варианта синтаксиса для Update и Delete. Но если проект начинался очень давно, то можно встретить и вот такой вариант написания:

update t1
set t1.col1 = 0
from table1 t1, table2 t2
where t1.col2 = t2.col2

Перед нами UnqualifiedJoin — соединение таблиц в стиле ANSI-89. И UnqualifiedJoin, и QualifiedJoin наследуются от JoinTableReference, поэтому просто заменим тип в FindTableReferenceByAlias на общий:

if (currentTableReference is JoinTableReference joinTableReference)

Теперь парсер обрабатывает оба варианта джойнов. 

Конечно, в рамках статьи не получится охватить все возможные случаи. Реальные варианты кода могут включать и другие конструкции. Например, вот здесь обновляемая таблица спрятана в недрах подзапроса:

update t
set data_number = 0
from (
	select
		data_number
	from #tmp_some_data
where text_err is null) t
join table2 t2 on t2.col1 = t.col1

Выглядит непривычно, но это немного упрощенный пример реального кода.  Для его разбора необходимо анализировать структуру подзапроса (который также может иметь вложенность и повторяющиеся алиасы на разных уровнях вложенности), опираясь на названия обновляемых колонок. Бывают случаи, когда обновляемую таблицу невозможно определить без обращения к словарю данных. За рамками статьи остался и вопрос с использованием CTE.

Полезные ссылки и дополнительная информация

Пример из статьи: https://github.com/Sergei-K128/FindUpdate

Сама библиотека на GitHub: https://github.com/microsoft/SqlScriptDOM

Для его использования ScriptDom необязательно писать приложение на C# , все можно сделать и на PowerShell. Иногда это удобнее, чем приложение на C#. Вот здесь можно посмотреть примеры на powershell: https://www.sqlservercentral.com/steps/stairway-to-scriptdom-level-2-parsing-tokens-and-the-abstract-syntax-tree

При знакомстве с парсером и разборе нетривиальных скриптов очень поможет визуализация дерева: https://github.com/GoEddie/ScriptDomVisualizerPrivate. В исходном коде этого приложения используется парсер 120 версии, что может давать некорректные результаты на скриптах позднейших версий, лучше поменять версию парсера на более современную.

Парсер может споткнуться на разборе скриптов с недокументированными фичами, например, при использовании колонки %%physloc%% . Я обошел эту проблему заменой %% на валидные идентификаторы (xoxophyslocxoxo) в тексте скрипта до разбора парсером. 

Один из недостатков библиотеки — автоматически сгенерированная документация.  С ее помощью можно ориентироваться среди классов, но для сложных случаев она малоинформативна – нет примеров, описание очень лаконичное. В задаче определения целевой таблицы для выражения DML TableReference оказывался самым используемым классом и я сделал краткую сводную таблицу классов-наследников: