Search stored procedures/functions in all databases

Question

I want to search for specific text in all procedures/functions etc. in all databases. I managed to create the required query from this answer but it looks like OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) returns NULL for all DBs except the current one.

sp_msforeachdb 'SELECT ''?'' AS DB, SPECIFIC_NAME, OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) FROM [?].INFORMATION_SCHEMA.ROUTINES'

Answer 1

You absolutely need Red-Gate's SQL Search tool - it's FREE, and absolutely great and perfectly suited for this need.