SQL Server 2005, Search string in Procedures and Functions

Use following sql script to search some string inside Stored procedure or User defined function, this is handy when you have lot of stored procedures and functions and you are looking for particular one, like i want to list procedures in which a specific Table has been used.

This query search in procedures and functions script stored in database schema.

USE DATBASENAME

GO

-- SYS COMMENTS
SELECT distinct so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE charindex('Search', text) > 0


--INFORMATION_SCHEMA.ROUTINES
SELECT routine_name, routine_type
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Search%'