SQL Server, How to find a text inside a SQL Server trigger or procedures?

Hello Friend,

Today we have a little but useful post.

I know many of you already know how to search for text inside a trigger or inside a stored procedure.

But for those who still don't know.

How to find a text inside a SQL Server trigger or procedures?

 

This is the simple T-SQL string:

       
SELECT

  DISTINCT O.NAME AS OBJECT_NAME,O.TYPE_DESC

FROM SYS.SQL_MODULES M

  JOIN SYS.OBJECTS  O ON M.OBJECT_ID=O.OBJECT_ID

WHERE

 M.DEFINITION LIKE '%<TEXT_TO_SEARCH_FOR>%'

 

 

A simple example..

 

Define a trigger:

 

CREATE TRIGGER TR_ORDRIG_UPD ON ORDRIG AFTER INSERT

AS

BEGIN

  UPDATE T

    SET T.TOTQTA1 = TOT.TOTQTA1

  FROM dbo.ORDTES T

    JOIN INSERTED I ON I.IDORDTES = T.ID

       CROSS APPLY (SELECT SUM(R.QTA1) AS TOTQTA1 FROM dbo.ORDRIG R WHERE R.IDORDTES = T.ID) AS TOT

END

 

And now execute the statement searching for T.TOTQTA1

The question is there is any trigger the read o write the TOTQTA1 field?

 

SELECT

  DISTINCT O.NAME AS OBJECT_NAME,O.TYPE_DESC

FROM SYS.SQL_MODULES        M

  JOIN SYS.OBJECTS  O ON M.OBJECT_ID=O.OBJECT_ID

WHERE

 M.DEFINITION LIKE '%T.TOTQTA1%'

 

 

Et voilà!

  

 

 

Well, that's all for today
Have a great december!
Luca

Previous post: SSMS 18.7.1 installation and news!