Traženje teksta u procedurama, funkcijama i trigerima na SQL Serveru

Izvorna funkcija iz 2017. godine

Bila mi je potrebna jedna takva opcija: da mogu da nađem tekst u funkcijama, trigerima i uskladištenim procedurama na MSSQL serveru. Nastala je ova funkcija, Find_Text_In_SP, koja spaja sistemske tabele sysobjects i syscomments i vraća naziv i tip svakog objekta u čijem se kodu nalazi traženi tekst.

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!%')
  )  

Šta rade filteri?

Uslovi u upitu isključuju objekte koji u pretrazi samo smetaju:

• MSmerge%: objekti koje SQL Server pravi za merge replikaciju.
• dt_%: stare sistemske procedure za dijagrame baze.
• sp_cft, sel_, sp_sel, sp_upd, sp_ins: prefiksi automatski generisanih procedura u projektu za koji je funkcija pisana. U drugoj bazi ove prefikse treba zameniti svojim ili ih ukloniti.
• -done!: oznaka kojom su u kodu obeležena mesta koja su već obrađena, pa se ne prikazuju ponovo.
• SO.Type: pretražuju se procedure (P), skalarne funkcije (FN), inline i tabelarne funkcije (IF, TF), trigeri (TR) i podrazumevane vrednosti (D).

Ograničenja izvorne verzije

• sysobjects i syscomments postoje samo radi kompatibilnosti sa SQL Serverom 2000 i Microsoft ih ne preporučuje za nov kod.
• syscomments čuva kod objekta u delovima od po 4000 znakova. Tekst koji pada na granicu dva dela neće biti pronađen.
• Pogledi (V) nisu obuhvaćeni pretragom.
• NOLOCK nad sistemskim tabelama ne donosi primetno ubrzanje; pretraga je brza zato što je katalog mali u odnosu na podatke u bazi.

Savremena verzija

Od SQL Servera 2005 kod svakog programabilnog objekta čuva se u prikazu sys.sql_modules, u koloni definition tipa nvarchar(max). Kod nije podeljen na delove, pa se tekst uvek pronalazi, a pretraga obuhvata procedure, funkcije, trigere i poglede.

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
);

Umesto LIKE koristi se CHARINDEX, tako da znakovi _, % i [ u traženom tekstu nemaju posebno značenje. Da li se razlikuju velika i mala slova zavisi od collation podešavanja baze. Uslov is_ms_shipped = 0 izostavlja objekte koje je instalirao sam SQL Server, a rezultat sadrži i šemu i datum poslednje izmene objekta.

Primer korišćenja

Obe funkcije se pozivaju kao tabela, sa traženim tekstom kao parametrom:

-- izvorna verzija
SELECT * FROM dbo.Find_Text_In_SP(N'Customers');

-- savremena verzija
SELECT *
FROM dbo.Find_Text_In_Modules(N'Customers')
ORDER BY ObjectType, ObjectName;

Ovakva pretraga je najkorisnija pre izmene strukture baze: pre nego što se kolona ili tabela preimenuje ili obriše, odmah se vidi koje procedure, funkcije i pogledi je koriste. Kako se baza planira da bi takve izmene bile što ređe opisano je u tekstu o izradi baze podataka.