If you want to search a particular text (table name, command name, part of any written code) in any of the stored procedure, VIEW, UDF or inline written code in any stored procedure or a function you can easily use the following small query.
Since, this is just a basic need of a day to day operations of a software/database developer, I’ve thought of sharing this in my blog that any interested individual can have a look.
In the following example I’ve attempted to search the word ‘itemmaster’ in all my written SPs and functions. Actually, itemmaster is just a table name in one of my databases.
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 ‘%itemmaster%’
Output of the above query,
Early Bites 