Вам когда-нибудь приходилось искать что-то, на что ссылается тело SQL-запроса? Возможно, вам нужно знать, какие запросы вам придется изменить для предстоящего переименования таблицы. Или, возможно, вы хотите узнать, сколько запросов на вашем сервере выполняются с использованием SELECT *.

В этой статье мы рассмотрим два шаблона, которые вы можете использовать для поиска по тексту SQL-запросов на вашем SQL Server.

1. Поиск в хранимых процедурах, функциях и представлениях

Если вас интересуют запросы, которые являются частью хранимой процедуры, функции или представления, то вы можете использовать представление sys.sql_modules для поиска по текстам запросов этих объектов базы данных. В этом представлении хранится текст запроса каждого модуля в вашей базе данных, а также другие свойства.

Вот шаблон, который вы можете использовать для поиска по текстам запросов хранимых процедур, функций и представлений:

USE <имя базы данных>;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    o.type_desc AS ObjectType,
    DB_NAME(o.parent_object_id) AS DatabaseName,
    s.name as SchemaName,
    o.name as ObjectName,
    r.Definition
FROM
    sys.sql_modules r
    INNER JOIN sys.objects o ON r.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
    -- введите ваш ключевое слово для поиска здесь
    r.Definition LIKE '%SELECT%'

Например, если вы хотите найти хранимые процедуры и функции, которые могут содержать уязвимости SQL-инъекции, вы можете изменить вышеуказанный шаблон, чтобы добавить дополнительную фильтрацию в раздел WHERE.

2. Поиск ад-хок SQL-запросов

Поиск по ад-хок запросам немного сложнее. Если вы не ведете превентивное журналирование текстов запросов с помощью расширенных событий или другого инструмента, нет способа определенно найти каждый текст ад-хок запроса. Однако SQL Server создает (или повторно использует) план выполнения для каждого выполняемого запроса. Большинство этих планов затем добавляются в кэш планов выполнения. Планы выполнения в конечном итоге удаляются из кэша по разным причинам, но пока они существуют, мы можем легко искать их содержимое, включая поиск по тексту запроса этого плана.

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

USE <имя базы данных>;
GO
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
    stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
    query_plan AS [QueryPlan]
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
WHERE
    -- введите ваши ключевые слова для поиска здесь
    stmt.value('(@StatementText)[1]', 'varchar(max)') LIKE '%SELECT%'

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

Хотя использование этой техники не позволит вам искать по 100% ад-хок запросов, она должна позволить искать те, которые выполняются наиболее часто и появляются в вашем кэше планов.

Спасибо за чтение. Следите за новыми советами и трюками по SQL Server!