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
JOINSYS.OBJECTS
O
ON
M.
OBJECT_ID
=O.
OBJECT_ID
WHERE
M.DEFINITION
LIKE
'%<TEXT_TO_SEARCH_FOR>
%'
A simple example..
Define a trigger:
CREATETRIGGER TR_ORDRIG_UPD ON ORDRIG AFTERINSERT
AS
BEGIN
UPDATE T
SET T.TOTQTA1 = TOT.TOTQTA1
FROM dbo.ORDTES T
JOIN INSERTED I ON I.IDORDTES = T.ID
CROSSAPPLY(SELECTSUM(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.NAMEASOBJECT_NAME,O.TYPE_DESC
FROMSYS.SQL_MODULES M
JOINSYS.OBJECTS O ON M.OBJECT_ID=O.OBJECT_ID
WHERE
M.DEFINITIONLIKE'%T.TOTQTA1%'
Et voilà!
Well, that's all for today
Have a great december!
Luca