How to find stored procedures by name?

I just 开发者_开发百科need to search through all the stored procedures on my database looking for one that contains "item" in its name. Any ideas?

I've been tinkering around with this, but it's not quite there yet:

SELECT DISTINCT OBJECT_NAME(ID) FROM SysComments WHERE Text LIKE '%Item%'

---

To find those that contain the string "Item" in the name.

select schema_name(schema_id) as [schema], 
       name
from sys.procedures
where name like '%Item%'

---

This can also help you on this issue. It also works when there are databases with different collations. And you can find procedure by passing either exact procedure name or part of its name.

---

You can use new Query in Server 2008:

use dbName
go

print object_definition(object_id('storedProcedureName'))

You will get contents of procedure.

---

With SQL, you can only use the % and _ wildcards. If you would like more powerful searching, you can use SchemaCrawler. SchemaCrawler can search for routines using regular expressions that match the name. You can even search within the routine definition using regular expressions.

Sualeh Fatehi, SchemaCrawler