SQL Server Get procedure by text content

Scenario : Find the list of procedures which contains the text - "datafinder"

Solution : To get the procedure with text content we need to query two tables - sys.procedures and sys.Sql_modules.

The query to get all the procedure containing text - "datafinder" is below

SELECT [Name] FROM sys.procedures AS A
INNER JOIN sys.sql_modules AS B
ON A.object_id = B.object_id
AND B.definition like '%datafinder%'

Explanation : The sys.sql_modules keep the definition details of any sql module like view, procedure or function by object_id and sys.procedure contains the procedure with its object_id. Since we are specifically looking for procedure containing a specific text so we joined sys.sql_modules with sys.procedures. Similarly we can do the same for SQL Server views as shown below.

SELECT [Name],definition FROM sys.views AS A

INNER JOIN sys.sql_modules AS B

ON A.object_id = B.object_id

AND B.definition like '%datafinder%'

Or for function we can do as below

SELECT B.name,definition FROM sys.sql_modules AS A

INNER JOIN sys.SYSobjects  AS B

ON A.object_id = B.id

WHERE B.type = 'FN'

AND B.definition like '%datafinder%'

SYSOBJECTS contains list of all system objects like function, table, procedures and views etc with different type column.