What is the keyboard method for filtering in SQL Management Studio?

Question

I make very heavy use of filtering in SQL Server Management Studio when working with a large number of stored procedures - but find it a slow process as I have to right click, move my mouse to select 'filter', type in filter, and then click OK.

Is there hotkey to pull up the filter dialog when I have the node I want to filter selected?

Thank you!

Answer 1

This is a really interesting question. I never use Filter precisely for the reason you mention.

There seem to be a lot of hotkeys for SSMS, but none for Object Explorer. Using Tools - Customize, custom hotkeys can be set for nearly every action except those of Object Explorer. There exists an Object Explorer Detail View, which you'd expect to have more features, but even that doesn't support easy shortcuts.

Triple bummer.

Rummaging around on the Internet, I happened upon a cool addin by Joseph Cooney called QuickFind, which docks near your object explorer window and accepts queries. The queried results can be clicked and the corresponding node in Object Explorer is opened. Not quite the same as a filtered view, but pretty nice nonetheless.

Apparently someone took Joseph's code and improved upon it, which led to DB Object QuickFind which I haven't tested. I think it has roughly the same functionality.

Good luck, and thanks for starting me off on my own search!

Answer 2

You can navigate to Tools > Options > Keyboard page > Short cut list - and here you can set any key board short cut keys for executing a stored procedure. You can define a stored procedure to search for a stored procedure and use this shortcut.

Or you can use this query to search for stored procedures -

SELECT A.NAME FROM SYSOBJECTS A (NOLOCK) WHERE
A.TYPE = 'P' AND A.NAME LIKE '%<filter_text>%'

If necessary you can perhaps create a function / stored procedure and use that to search stored procedures easily -

ALTER Function FindSP(@StringToSearch varchar(100))
returns table
AS
return
(

SELECT A.NAME FROM SYSOBJECTS A (NOLOCK) WHERE
    A.TYPE = 'P' AND A.NAME LIKE '%' + @StringToSearch + '%'  
)

And you can simple filter your stored procedures by using a select query -

select * from FindSp('<your_search_term>')