SQL SERVER 2005 – Search Stored Procedure Text

    

How to 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?

The below script explains how to search all procedures 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