Тражење текста у процедурама, функцијама и тригерима на SQL Серверу

Изворна функција из 2017. године

Била ми је потребна једна таква опција: да могу да нађем текст у функцијама, тригерима и ускладиштеним процедурама на MSSQL серверу. Настала је ова функција, Find_Text_In_SP, која спаја системске табеле sysobjects и syscomments и враћа назив и тип сваког објекта у чијем се коду налази тражени текст.

CREATE FUNCTION [dbo].[Find_Text_In_SP]
(
  @String1ToSearch nvarchar(100)
)
returns table 

AS
   
  RETURN
  (
     SELECT Distinct SO.Name, so.type
     FROM sysobjects SO (NOLOCK)
      INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
      AND NOT(SO.Name LIKE N'MSmerge%')
      AND NOT(SO.name LIKE N'dt_%') 
      AND (not (SO.name in (N'GrantExectoAllProcedures_sp')))
      AND (left(SO.name, 6) <> N'sp_cft')
      AND (left(SO.name, 4) <> N'sel_')
      AND (left(SO.name, 6) <> N'sp_sel')
      AND (left(SO.name, 6) <> N'sp_upd')
      AND (left(SO.name, 6) <> N'sp_ins')
      AND SO.Type IN (N'P',N'FN',N'IF',N'TF',N'TR',N'D')
    WHERE (SC.Text LIKE N'%' + @String1ToSearch+ N'%')
      and NOT(SC.Text LIKE N'%' + @String1ToSearch+ N'-done!%')
  )  

Шта раде филтери?

Услови у упиту искључују објекте који у претрази само сметају:

• MSmerge%: објекти које SQL Server прави за merge репликацију.
• dt_%: старе системске процедуре за дијаграме базе.
• sp_cft, sel_, sp_sel, sp_upd, sp_ins: префикси аутоматски генерисаних процедура у пројекту за који је функција писана. У другој бази ове префиксе треба заменити својим или их уклонити.
• -done!: ознака којом су у коду обележена места која су већ обрађена, па се не приказују поново.
• SO.Type: претражују се процедуре (P), скаларне функције (FN), inline и табеларне функције (IF, TF), тригери (TR) и подразумеване вредности (D).

Ограничења изворне верзије

• sysobjects и syscomments постоје само ради компатибилности са SQL Сервером 2000 и Microsoft их не препоручује за нов код.
• syscomments чува код објекта у деловима од по 4000 знакова. Текст који пада на границу два дела неће бити пронађен.
• Погледи (V) нису обухваћени претрагом.
• NOLOCK над системским табелама не доноси приметно убрзање; претрага је брза зато што је каталог мали у односу на податке у бази.

Савремена верзија

Од SQL Сервера 2005 код сваког програмабилног објекта чува се у приказу sys.sql_modules, у колони definition типа nvarchar(max). Код није подељен на делове, па се текст увек проналази, а претрага обухвата процедуре, функције, тригере и погледе.

CREATE FUNCTION [dbo].[Find_Text_In_Modules]
(
  @SearchText nvarchar(200)
)
RETURNS TABLE
AS
RETURN
(
  SELECT
    s.name        AS SchemaName,
    o.name        AS ObjectName,
    o.type_desc   AS ObjectType,
    o.modify_date AS ModifiedDate
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o ON o.object_id = m.object_id
  INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
  WHERE o.is_ms_shipped = 0
    AND CHARINDEX(@SearchText, m.definition) > 0
);

Уместо LIKE користи се CHARINDEX, тако да знакови _, % и [ у траженом тексту немају посебно значење. Да ли се разликују велика и мала слова зависи од collation подешавања базе. Услов is_ms_shipped = 0 изоставља објекте које је инсталирао сам SQL Server, а резултат садржи и шему и датум последње измене објекта.

Пример коришћења

Обе функције се позивају као табела, са траженим текстом као параметром:

-- изворна верзија
SELECT * FROM dbo.Find_Text_In_SP(N'Customers');

-- савремена верзија
SELECT *
FROM dbo.Find_Text_In_Modules(N'Customers')
ORDER BY ObjectType, ObjectName;

Оваква претрага је најкориснија пре измене структуре базе: пре него што се колона или табела преименује или обрише, одмах се види које процедуре, функције и погледи је користе. Како се база планира да би такве измене биле што ређе описано је у тексту о изради базе података.