Easily find one stored procedure in SQL Server Management Studio from 1000 in treeview?

Question

Our database is about to reach 1000 stored procedures. Although we were wise and created a good scheme for naming the stored procedures, hunting for the stored procedure you need can be a bit frustrating as you scroll and scroll and scroll. If I know the exact name of the stored procedure I'm looking for, it would be great to identify it quickly and modify

For example, if I want to find p_Blah_Blah in the treeview and open it to modify it, is there a command to launch p_Blah_Blah in a new window for editing? Or perhaps a plugin that limits the view of stored procedures to what you are searching for? Anyone who wants to write a plugin? :)

Answer 1

You can filter the tree, right click on "Stored Procedures" choose "Filter" and "Filter Settings".

As a general rule here we use naming conventions to do with context, for example all user sprocs are named: sp_user_*

Answer 2

In Management Studio 2005 you can right click on the stored procedures folder (and other folders too) to set a filter, then you can type in all, or part, of the names that you are looking for.

If you just want to know details of parameters (or rows for a table etc.) then in a query window you can type the objects name, highlight it, and press alt-F1 to call sp_Help on the object.

Answer 3

EXEC sp_HelpText 'your procedure name'

then proceed to modifying it.