Searching for SQL Stored Proc

I wanted to look for a Stored Proce by its name.
foung this SQL
SELECT ROUTINE_NAME as 'Store proc' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%hello how%' and ROUTINE_TYPE='procedure'
that looks for a string in the SP.
way more then what i wanted.

in SQL server 2005 the situation is a bit more simple
SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%foobar%'
SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE Definition LIKE '%foobar%' AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%foobar%' AND ROUTINE_TYPE = 'PROCEDURE'

found the best post on the subject

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.