How to find a text inside SQL Server procedures / triggers?
here is a portion of a procedure I use on my system to find text....
DECLARE @Search varchar(255)SET @Search='[10.10.100.50]'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
You can find it like
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'It will list distinct stored procedure names that contain text like 'User' inside stored procedure. More info
[Late answer but hopefully usefull]
Using system tables doesn't always give 100% correct results because there might be a possibility that some stored procedures and/or views are encrypted in which case you'll need to use DAC connection to get the data you need.
I'd recommend using a third party tool such as ApexSQL Search that can deal with encrypted objects easily.
Syscomments system table will give null value for text column in case object is encrypted.