Проблема: Как я могу точно найти, какие хранимые процедуры, представления или функции SQL Server используют определенную текстовую строку, которая может быть именем таблицы или любой строкой, которая является частью кода?

Решение:

Это очень распространенная проблема, и уже существует множество решений на T-SQL. Однако у общего решения есть некоторые недостатки. Давайте рассмотрим пример: скажем, я хочу найти любую хранимую процедуру, представление или функцию, содержащую таблицу с именем tblABC.

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

-- Применимо для SQL 2005 и более поздних версий
USE [Ваша_БД];
GO

SELECT [Схема] = schema_name(o.schema_id), o.Name, o.type 
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition like '%tblABC%'
GO

Два очевидных проблемы с этим решением:

1. Если у вас есть таблица с именем tblABC123, результат будет включать объекты, содержащие tblABC123, а не только tblABC.
2. Если tblABC включен в комментарии, результат будет включать объекты, содержащие такие комментарии.

Лучшее решение для поиска кода T-SQL:

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

На самом деле, это немного избыточное решение по сравнению с исходным вопросом, но помимо устранения вышеупомянутых проблем, это решение может предоставить удобные идеи для решения других интересных вопросов, таких как наличие команд INSERT, UPDATE или DELETE в хранимой процедуре, сколько раз таблица используется в хранимой процедуре, используются ли временные таблицы и т. д.

Дизайн решения выглядит следующим образом:

1. Определите и создайте таблицу для хранения результата
2. С помощью PowerShell переберите все хранимые процедуры, представления или функции базы данных и с помощью класса .NET RegEx удалите комментарии, а затем разделите каждое слово в тексте каждого объекта
3. Запишите результат в таблицу

Вот исходный код:

1. Создайте таблицу SQL Server для хранения результатов:

-- Применимо для SQL 2005 и более поздних версий
-- выполните в окне SSMS
USE [TempDB]; -- измените на свою базу данных
GO

CREATE TABLE dbo.tblWord (
    id int identity primary key,
    DBName varchar(80),
    ObjectName varchar(80),
    ObjectType varchar(10),
    Word varchar(80),
    [Count] int
)
GO

2. Скрипт PowerShell для разделения кода хранимой процедуры. Чтобы проверить представления, UDF или триггеры, просто измените $type в следующем скрипте:

#Requires -Version 3.0
#Requires -Modules sqlserver
import-module sqlserver -DisableNameChecking;
 
function MatchEval-Function # в качестве делегатной функции
{ 
    param ([string]$x)
   
    if ($x.StartsWith('/*') -or $x.StartsWith('--'))
    { 
        return "";
    }
    else 
    {
        return $x;
    }
}

[string]$ServerName ='TP_W520' # измените на имя вашего SQL сервера
 
[string]$database = 'AdventureWorks2012'; # измените на свое собственное имя базы данных
[string]$type = 'sp' # 'sp' | 'vw' | 'udf' | 'trg';
 
# любые слова в $filter_words не будут учитываться 
[string[]] $filter_words='begin', 'end'; # вы можете добавить все, что хотите отфильтровать

[string]$object=''

switch ($type)
{
   'vw' {$object='Views'; break;}
   'sp' {$object='StoredProcedures'; break;}
   'udf' {$object='UserDefinedFunctions'; break;}
   'trg' {$object='Triggers'; break;}
}
 
$rslt = New-Object System.Data.DataTable;
$col = New-Object System.Data.DataColumn('DBName', ([String]))
$rslt.columns.Add($col);

$col = New-Object System.Data.DataColumn('ObjectName', ([String]))
$rslt.columns.Add($col);
$col = New-Object System.Data.DataColumn('ObjectType', ([String]))
$rslt.columns.Add($col);
$col = New-Object System.Data.DataColumn('Word', ([String]))
$rslt.columns.Add($col);
$col = New-Object System.Data.DataColumn('Count', ([Int]))
$rslt.columns.Add($col);

# $Reg_exp_1 используется для поиска всех обычных комментариев, если комментарий находится в кавычках, он игнорируется
[string]$reg_exp_1 = @'
["'][^'"]*?['"]|(?m)--.*$|(?s)/\*.*?\*/[\r\n]?
'@;
 
$RegEx= new-object "System.Text.RegularExpressions.RegEx" ($reg_exp_1);
 
$reg_exp_2 = '(?m)^[;\s]*$[\r\n]'; # удалить пустые строки
$RegEx2= new-object "System.Text.RegularExpressions.RegEx" ($reg_exp_2);
 
## это для замены всех множественных пробелов, символа '=' и '.' на один пробел
$reg_replace = '\t{1,}|\r\n|\s{2,}|=|\.|,|\(|\)';
$RegEx_Replace = new-object "System.Text.RegularExpressions.RegEx" ($reg_replace);
 
# эти символы будут удалены ;+[]'
$reg_remove = @'
\(\d+\)|;|'|\+|\[|]
'@;
 
$RegEx_Remove = new-object "System.Text.RegularExpressions.RegEx" ($reg_remove);
 
# если ваш экземпляр SQL не является экземпляром по умолчанию, вам нужно изменить "default" на имя вашего экземпляра SQL
dir sqlserver:\sql\$ServerName\default\databases\$database\$object |
% {
    $o = $_;
    $s = $_.textheader + $_.textbody;
    $ss = $RegEx.replace($s, {MatchEval-Function $args[0].value}); # удалить все комментарии
    $ss = $RegEx2.replace($ss, ''); # удалить все пустые строки;

    $ss = $RegEx_Remove.replace($ss, '');
 
    $ss = $RegEx_replace.replace($ss, ' ');
 
 
    $ss.split(' ') | where {($_.length -gt 0) -and ($_ -notin $filter_words) }  |
    select @{l='ObjectName'; e={$o.Schema+'.'+$o.name}}, @{l='Word'; e={$_}}  |
    Group-Object -Property ObjectName, Word -NoElement |
    % { 
        $r = $rslt.NewRow(); 
        $r.DBName = $database;
        $r.ObjectName=($_.Name -split ', ')[0]; 
        $r.ObjectType = $Type;
        $r.Word=($_.Name -split ', ')[1];
        $r.Count = $_.Count;
        $rslt.Rows.add($r);
      }
}

# мы должны подготовиться к записи собранной информации в центральное место. 
$col_mapping = @{};
$col_mapping.Add('DBName','DBName'); # в формате (sourceColumn, destinationColumn)

$col_mapping.Add('ObjectName','ObjectName');
$col_mapping.Add('ObjectType','ObjectType');
$col_mapping.Add('Word','Word');
$col_mapping.Add('Count','Count');
 
# нужно заменить TempDB на свои собственные настройки среды
# вставить разделенное слово в предопределенную таблицу
$conn = New-Object System.Data.SqlClient.SqlConnection ("Server=$ServerName; Database=TempDB; trusted_connection=TRUE");
$conn.Open();
$bulkcopy = New-Object System.Data.SqlClient.SqlBulkCopy($conn);
 
$col_mapping.keys | % {$bc_mapping = new-object System.Data.SqlClient.SqlBulkCopyColumnMapping($_, $col_mapping[$_]); $bulkcopy.ColumnMappings.Add($bc_mapping); } | Out-Null;
$bulkcopy.DestinationTableName='dbo.tblWord'; # вы можете изменить на свою таблицу
$bulkcopy.WriteToServer($rslt);

3. Мы можем проверить, какие хранимые процедуры используют какие таблицы:

USE AdventureWorks2012;
GO

SELECT w.ObjectName, [TableName] = t.name, w.[Count] 
FROM sys.tables t
INNER JOIN tempdb.dbo.tblWord w ON t.name = w.word;
GO

Примечание: столбец [Count] может иметь большее значение, чем должно быть на самом деле. Например, для таблицы Person.Person, то есть схема и имя таблицы оба называются “Person”, значение [Count] будет удвоено для слова “Person”.

Чтобы проверить все пустые таблицы, используемые в хранимых процедурах:

USE AdventureWorks2012;
GO

SELECT w.ObjectName, [TableName] = t.name, [RowCount] = p.Rows 
FROM sys.tables t
INNER JOIN tempdb.dbo.tblWord w ON t.name = w.word
INNER JOIN sys.partitions p on p.object_id = t.object_id and p.rows = 0 and p.index_id < 2
GO

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