CODEWITHSUNDEEP
sqlsybase
Aniruddha
Aniruddha

Reputation: 3327

Find all stored procedures with particular starting name

I have many stored procedures in my database. I have to transfer a few of them to a different server.

I need to find the procedures starting with "ABC".

How do I search all these stored procedures?

Upvotes: 0

Views: 4543

Answers (3)

user1786058
user1786058

Reputation:

a little tweak of Adam Wenger answer, that worked for me:

SELECT o.name
FROM sysobjects AS o
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS p ON o.name = p.SPECIFIC_NAME
WHERE o.name LIKE '%price%'

OR 

SELECT o.name
FROM sysobjects AS o
INNER JOIN sys.procedures AS p ON o.id = p.object_id
WHERE o.name LIKE '%price%'

Upvotes: 2

noonand
noonand

Reputation: 2865

No join necessary here and uses the INFORMATION_SCHEMA view (available since SQL Server 2005)

SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME LIKE 'ABC%'
ORDER BY SPECIFIC_NAME ASC

Upvotes: 0

Adam Wenger
Adam Wenger

Reputation: 17570

According to the documentation, this query should give you the procedures that start with "ABC":

SELECT o.name
FROM sysobjects AS o
INNER JOIN sysprocedures AS p ON o.id = p.id
WHERE o.name LIKE "ABC%"

Upvotes: 3

Related Questions