Method 1:-
SELECT
SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE '%searchtext%'
--Option 1
SELECT DISTINCT so.NAME
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE sc.TEXT LIKE '%Employee%'
GO
--Option 2
SELECT DISTINCT o.NAME
,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id = o.id
WHERE c.TEXT LIKE '%Employee%'
GO
--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
Refrencing http://blog.sqlauthority.com/2007/11/10/sql-server-2005-2000-search-string-in-stored-procedure/