I am using syscomments table to find any query or text from within the procedures etc. When I try to find out a text value from within the trigger using syscomments system object. It is not returning the required results. After little research I find out another query that will search all objects in your database. I tried it and it is working fine. This query will work from SQL 2005 to all SQL Server versions.
Please try and share if it is helpful.
DECLARE @Search varchar(255)
SET @Search='SET @TypeID = 66'
SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1
Please try and share if it is helpful.
DECLARE @Search varchar(255)
SET @Search='SET @TypeID = 66'
SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1
