CODEWITHSUNDEEP
databasesql-server-2008stored-proceduresmetadata
user489041
user489041

Reputation: 28304

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%'

Upvotes: 6

Views: 17559

Answers (4)

Sualeh Fatehi
Sualeh Fatehi

Reputation: 4784

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

Upvotes: 1

HaikMnatsakanyan
HaikMnatsakanyan

Reputation: 303

You can use new Query in Server 2008:

use dbName
go

print object_definition(object_id('storedProcedureName'))

You will get contents of procedure.

Upvotes: 1

NeverHopeless
NeverHopeless

Reputation: 11233

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.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453298

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%'

Upvotes: 7

Related Questions