I am thankfull of Nandan Kumarawat who gave me this one. After asking him same thing time to time I blogged it here to make his life easy 😛
So let’s see some code now.
Query get you all the objects under the selected database containing the “text” set in sql variable. And also get you the object type. So you are good to know if this is stored procedure or view or anything-else.
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
And here the output looks like: