Searching string in all Store procedures in database ~ gniithelp ~ programming info

I'll show you how to do search trough all SP in one database to find a sting , text .

It's very useful part of MS SQL code :

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%user_name()%'

AND ROUTINE_TYPE='PROCEDURE'

select * from sysobjects o inner join syscomments c on o.id=c.id

where c.text like '%user_name()%'

Here you can see two simplest piece of Sql code that executes search trough all Store procedure in selected database for termin "user_name()" . It is very fast and useful .