CODEWITHSUNDEEP
sql-server
Tharif
Tharif

Reputation: 13971

How to search through 1000 stored procedures in SQL Server

Currently I have a database with nearly 1000 stored procedures. Whenever I need to check for any stored procedure, it takes a lot of time. Is there a way to search / browse the required stored procedure by just typing the name of stored procedure?

Thanks

Upvotes: 1

Views: 127

Answers (4)

Hell Boy
Hell Boy

Reputation: 981

In Object Explorer:

Step1: Right Click on Stored Procedures category in required database and select Filter > Filter Settings:

enter image description here

Step 2: In the Filter Settings dialog box, specify "Sales" in Value field, and click on OK to apply filter

enter image description here

After applying this filer Object Explorer will only show stored procedures matching the specified filter criteria, only those which have word "Sales" in their name in this case:

enter image description here

Step 3: To clear the filter and list all objects again, right click on filtered category and select Filter > Remove Filter. This will clear the filter applied.

Upvotes: 0

ps_prakash02
ps_prakash02

Reputation: 583

Try this

SELECT * FROM [sys].[procedures] WHERE [NAME] = 'YOUR_SP_NAME'

SELECT * FROM [dbo].[sysobjects] WHERE [XType] = 'P' AND [NAME] = 'YOUR_SP_NAME'

Upvotes: 0

Dhaval
Dhaval

Reputation: 2379

1) Select Store Prcedure Folder

2)Than Click On Filter button AS Shown in Image

3)Than Enter SP name and Click On OK Button

enter image description here

enter image description here

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82504

You could use a 3rd party add in to ssms to search your database, or you can query sys.procedures. to view the content of a procedure you can execute sp_helptext like this: EXEC sp_helptext '<Your stored procedure name>'

Upvotes: 1

Related Questions