Search stored procedures/functions in all databases

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 li开发者_如何学Goke 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'

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

The problem is OBJECT_ID cannot be used that way. It only works on the current database. Try returning ROUTINE_DEFINITION directly from INFORMATION_SCHEMA.ROUTINES. This does have a limit of 4000 characters. I'll try to find my other answer on SO which gives my workaround using the MS metadata views.

Have a look at this:

Can you search SQL Server 2005 Stored Procedure content?

try this:

select * from syscomments where [text] like '%yourKeyword%'