How to find all the Stored Procedures having a given text in it?

How to find all the Stored Procedures having a given text in it?

Below screen shot illustrates the difference between these two queries:

Script:

SELECT OBJECT_NAME(OBJECT_ID),

definition

FROM sys.sql_modules

WHERE definition LIKE '%' + 'cid' + '%'

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),

object_definition(OBJECT_ID)

FROM sys.Procedures

WHERE object_definition(OBJECT_ID) LIKE '%' + 'CId' + '%'

SELECT obj.Name SPName, sc.TEXT SPText

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'CId' + '%'

AND TYPE = 'P'

SELECT table_catalog,table_name,column_name,data_type,column_default

FROM information_schema.columns

WHERE column_name LIKE '%' + 'CId' + '%'