How to find stored procedures by name?

Question

I just need to search all the stored procedures in my database that are looking for the one that contains the "element" in its name. Any ideas?

I do this, but not quite there yet:

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

Answer 1

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

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

Answer 2

It can also help you solve this problem. It also works when there are databases with different sorts. And you can find the procedure by passing either the exact name of the procedure or part of its name.

Answer 3

Server 2008:

use dbName
go

print object_definition(object_id('storedProcedureName'))

You will receive the contents of the procedure.

Answer 4

With SQL, you can only use wildcards% and _. If you need a more powerful search, you can use SchemaCrawler . SchemaCrawler can search for routines using regular expressions matching the name. You can even search within the standard definition using regular expressions.

Sualeh Fatehi, SchemaCrawler