Often during development and maintenance of applications, the biggest issue for the developer is searching dependent objects in SQL server. These are generally the stored procedures, views and functions. The SQL server stores these procedures, views and functions in Tables. We just have to search the right tables for results.
Searching Stored Procedures, user defined Functions and Views
The below T-SQL query is used for searching stored procedures and function for a specific keyword.
SELECT Tsu.name AS schemaname, Tso.name AS routinename, Tsc.[text] AS routine, Tso.xtype AS routinetype
FROM sys.sysobjects AS Tso INNER JOIN sys.syscomments AS Tsc ON Tso.id = Tsc.id
INNER JOIN sys.sysusers AS Tsu ON Tso.uid = Tsu.uid
WHERE (Tso.xtype = 'P' OR Tso.xtype = 'v' OR Tso.xtype = 'FN') AND (Tsc.text LIKE '%Search Keyword%')
ORDER BY routinetype, routinename