Публикация
MSSQL
Поиск по хранимкам

Поиск по хранимкам в MsSQL

Караша Максим Юрьевич
20 апреля 2012 15:13

Часто случается, что нам в хранимках БД необходимо найти упоминания о каких-либо константах, которые, например, собираетесь удалить. И будет хорошо, если вы помните, где именно их использовали, а что если нет?

Так вот, далее я приведу пример нескольких запросов поиска по телу хранимок, триггеров и т.д.
Первый запрос осуществляет поиск по телу функций или процедур:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your text for search%'
--AND ROUTINE_TYPE='PROCEDURE'
--OR ROUTINE_TYPE='FUNCTION'

Следующий запрос делает выборку из всех объектов, существующих в БД:

SELECT [type], [name], OBJECT_DEFINITION([object_id])
FROM sys.objects
WHERE [type] IN ('P','TR','FN') AND OBJECT_DEFINITION([object_id]) LIKE N'%Your text for search%';

Но sys.objects не показывает триггеры DDL, так как они не принадлежат области схемы. Все триггеры (DML и DDL) найдены в таблице sys.triggers, которая поддерживает смешанные правила имен для различных видов триггеров. (Материал с MSDN)

Какие существуют типы объектов:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

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

SELECT DISTINCT so.name, sc.TEXT
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Your text for search%'

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

По просьбе Артёма написана процедура, возвращающая список объектов (название и тип), содержащих указанный текст.

CREATE PROCEDURE [dbo].[tsp_GetObjectsContainingText]
(
        @Text VARCHAR(Max),
        @Types VARCHAR(250) = 'P,TR,FN,TF'
)
AS
BEGIN
        SET NOCOUNT ON;
                       
        SELECT [name], [type_desc]
        FROM sys.objects
        WHERE EXISTS (
                SELECT d.Part FROM dbo.fn_ParseString(@Types, ',') d
                WHERE d.Part COLLATE  Cyrillic_General_CI_AI = [type] COLLATE  Cyrillic_General_CI_AI
        ) AND OBJECT_DEFINITION([object_id]) LIKE '%' + @Text + '%';
               
END

2-й входящий параметр Types по умолчанию = 'P,TR,FN,TF'.

Также для работы процедуры необходима функция fn_ParseString. Ниже её реализация:

CREATE FUNCTION [dbo].[fn_ParseString] (
        @ParseString nvarchar(4000), @Delimiter nvarchar(100))
returns @Result TABLE (
        [Line] int NOT NULL identity(1, 1),
        [Part] nvarchar(4000)
)
AS
begin
        declare @Part nvarchar(4000)
        declare @INDEX int
        SET @Delimiter = '%' + @Delimiter + '%'
        while (@ParseString > '')
        begin
                SET @INDEX = patindex(@Delimiter, @ParseString)
          IF (@INDEX > 0)
          begin
                SET @Part = substring(@ParseString, 1, @INDEX -1)
                        INSERT INTO @Result SELECT rtrim(ltrim(@Part))
                SET @ParseString = substring(@ParseString, @INDEX +1, len(@ParseString))
          end else
          begin
            SET @Part = substring(@ParseString, 1, len(@ParseString))
                INSERT INTO @Result SELECT rtrim(ltrim(@Part))
            break
          end
        end
  RETURN
end

Нравится

Поделиться

4 комментария
Репко Артём
21 апреля 2012 13:54

Максим, спасибо за ценную информацию!
Я бы добавил в список поиска также и

"Караша Максим Юрьевич" написал:
TF = SQL table-valued-function

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

Кривонос Максим
23 апреля 2012 12:51

Да, присоединяюсь к пожеланиям Артёма.
Нужно реализовать это всё в базовом функционале TSAdmin - в окне Greap Search!

Гомозов Олег
24 апреля 2012 08:59

Да, чтоб разработчики этим пользовались, лучше разместить в уже известном месте, в GREP-search

Караша Максим Юрьевич
24 апреля 2012 10:41

Для Оракла не нашёл корректного поиска.
Вот как вариант:

  select DISTINCT user_objects.object_name, user_objects.object_type from user_objects 
  JOIN sys.user_source ON sys.user_source.name = user_objects.object_name
  WHERE user_objects.object_type in ('PROCEDURE','FUNCTION','TRIGGER')
  AND sys.user_source.Text like '%text%'

Но недостатки запроса в том, что в таблице sys.user_source тело хранимок разбито на много записей, содержащихся в нескольких рядках.
Например небольшой цикл может занимать несколько записей:
1. BEGIN
2. LOOP
3. -- do something with the data
4. END LOOP;

И уже поиск по условию 

sys.user_source.Text like '%BEGIN%LOOP%'

ничего не вернёт.

Может кто знает более универсальный вариант?

Показать все комментарии