/ SQL Server

Searching in Stored Procedures

I work with developers a lot, and I see them reaching for tools like Red Gate's SQL Search, which is a cool free tool, but they don't realise much of that functionality is already baked in to SQL Server.

If all you need to do is check for a string in a database's stored procs, then this query will see you right:

SELECT '[' + SCHEMA_NAME(schema_id) +'].[' + [name] + ']' AS [Stored Procedure], 
	OBJECT_DEFINITION(object_id) AS [SP Definition]
FROM   sys.procedures
WHERE  OBJECT_DEFINITION(object_id) LIKE '%text%'