Search for a stored procedure

September 3, 2007 by Pinal Dave
SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

I receive following question many times by my team members.

How can I find if particular table is being used in the stored procedure?
How to search in stored procedures?
How can I do dependency check for objects in stored procedure without using sp_depends?

I have previously wrote article about this SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure.

The same feature can be implemented using following script in SQL Server 2005.
USE AdventureWorks
GO
--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO
--Searching for Empoloyee table and RateChangeDate column together
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GO
ResultSet:
Name
———————————–
uspGetEmployeeManagers
uspGetManagerEmployees
uspUpdateEmployeeHireInfo
uspUpdateEmployeeLogin
uspUpdateEmployeePersonalInfo

Name
———————————–
uspUpdateEmployeeHireInfo

Reference : Pinal Dave (http://blog.SQLAuthority.com)