Filtering in SSMS

Categories: News, Professional, SSC
Tags: No Tags
Comments: 1 Comment
Published on: December 21, 2011

Do you spend seconds, maybe even minutes trying to find things in SSMS?  Ever find yourself scrolling up and down thrown the tree trying to find that one specific object you seek?

Did you know it doesn’t have to be that way?  Microsoft has provided us the tools to help improve our efficiency in these matters.  Have you heard of filters?  I’m sure you have.  Did you know that there is a filter ability in SSMS?

Let’s see how that can be done using the master database in our examples.  I have expanded the tree to get down to the System stored procedures in the master database within SSMS.  It looks something like the following image.

From here, you can see that the tree looks pretty much like any other master database when viewing the stored procedures.  You have miles of stored procedures to scroll through.  Should you need to modify a stored procedure (in one of the user databases) this can be a bit cumbersome.

So let’s see what we can do to simplify this a little bit.

When you right click on the folder that contains your object you need to find, you are presented with a context menu.  One of the options on this context menu is “Filter.”

If we follow the menus, we will get something like this.

If you click on Filter settings, you will get a new window like this:

For this example, I have chosen to filter on the term “sp_helptext.”  Once I click OK, I will see a new filtered tree in SSMS that looks like this.

As you can see, I have just reduced my list of objects substantially.  I only have one stored procedure in this case to look at.  If in a user database and I need to modify that proc, then I can more quickly get to work.  This is especially handy if you have thousands of procs, or the procs have extraordinarily long names, or there numerous procs that are similar in name, or in my case – all of the above.

There you have it, my quick and easy Holiday gift to you this Holiday season to help you become more efficient.  BTW, thanks to a friend for pointing this out to me (Jack knows who he is 😉 ).

1 Comment - Leave a comment
  1. Dugi says:

    Hi Jason, this is very nice possibility in SSMS!
    Most of the DB Developers didn’t know about it, I’m talking for my location!
    Thanks for reminder!

Leave a comment

Your email address will not be published. Required fields are marked *

December 2011
« Nov   Jan »


  • @Ko_Ver: @NickyvV no, no special tricks in the package :) #sqlhelp
  • @NickyvV: @Ko_Ver I've seen that last week that the Job (step) succeeded, but it actually failed with an error #sqlhelp
  • @NickyvV: @Ko_Ver Is FailParentOnFailure set to false on a task/container maybe? #sqlhelp
  • @Ko_Ver: When I start an Agent job, the "job running" pop-up says there's an error. But SSIS catalog and Agent history says jobs succeed. #sqlhelp
  • @DbaMayor: #sqlhelp I'm seeing LCK_M_U and LCK_M_RS_U waits on sec readonly replica with only reporting queries...anyone faced similar issue?..thnks
  • @TrustMeImADBA: Prob not a good question for #sqlhelp but what is the chances time-service changing the time 23 sec forward made my sql cluster failover?
  • @zippy1981: Seem to be that I need to install SSMS vNext Verifying now #sqlhelp
  • @ChrisAdkin8: @YaHoznaDBA you should also compare avg writelog waits to avg tran log write stall,the two are not necessarily foced to be the same #sqlhelp
  • @toddkleinhans: #sqlhelp Will try using lock priority. Totally forgot about that. Thanks!
  • @DBArgenis: @toddkleinhans Compatibility mode is for T-SQL constructs and Query Optimization, nothing else. #sqlhelp

Welcome , today is Tuesday, February 21, 2017