Filtering Tables, Procedures, And Other Objects In SSMS

Published on: 2018-06-26

While the text of this post contains good information on SSMS object filters, I highly recommend watching this week’s video on YouTube – I stretched my creativity with filming this week while I was on vacation.

This week I’m sharing a trick I learned at Jess Pomfret‘s Ohio North SQL Server User Group presentation on data compression.  Her presentation on data compression was awesome (check it out if you get the chance), but I was shocked that I have been working with SSMS for so many years and have never known about the following trick I watched her perform in her demos.

An “Organized” Nightmare

I’m guessing you’ve probably worked in a database that has hundreds or thousands of database objects:

I often come across this in applications where for one reason or another someone decides that there is no need to separate apps into different databases; why bother creating different databases when you can have lots of different schemas to organize your objects instead!? (*cough* vendor applications *cough*).

The problem with these enormous lists of tables, procedures, functions, etc… is that it can get pretty tiring to scroll through them to find what you need.

For years I wore down my mouse’s scroll wheel, scrolling between thousands of objects across multiple server instances.  As it turns out, SSMS has a much better feature for handling this problem.

Filtering Objects in SSMS

You can apply filters to most objects in SSMS by right clicking and choosing “Filter Settings”:

You can filter on attributes such as name, schema, create date, etc…:

For example, if I want to see only tables that are in the Travel schema, all I have to do is create a filter:

SSMS will even tell me that my list of objects is filtered so I don’t go crazy later on wondering where all of my other tables went.

Limitations

There are a few limitations with using SSMS object filters though.

For example, the different filter attributes work together as if they had “AND” operators between them, so you can do something like filter on tables in the Travel schema that contain the letter “a”:

However, there is no way to write multiple conditions with OR logic (eg. you can’t filter on the schemas “Travel” OR “Lodging”).

And while SSMS will indicate that your objects are filtered, it won’t persist that filter after restarting SSMS.

Even with those drawbacks, I’ve used this filtering feature at least once per week since learning about it; it saves a lot of time and I can’t believe I went so long without knowing about it.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

9 thoughts on “Filtering Tables, Procedures, And Other Objects In SSMS”

  1. Great tip! Thanks for that one, in my DW work there are lots of tables I don’t need to see, but never knew you can filter on them :D.

    Did you know of the query screen function where you can put the output of a query in a different tab? Rightclick in the query window -> query options -> results. There you can display the results in a different tab. You can discard them as well, for example when in a demo for execution plans.

    1. Wow cool, thanks for sharing Reitse! That’s a useful one too I never knew about. Definitely will use that when presenting.

    2. I find somehow “creepy” that version 2017 of a server engine administration tool, like m$ one, that still lacks features like possibility of using wildcards in the filters.
      I need to filter eg: database objects that starts with a certain string, and this won’t work.
      There will be no difference between objects named: dbo.TLP_MyData and dbo.Proc_TLP_MyData.
      Would have been so easy for ppl@microsoft to add a “start with” / “ends with” filters and not just “contains”. In the end, those filters are almost useless, pointless, and you end up writing your query to filter objects. Too bad!!!

      1. Agreed. SQL Server 2019 SSMS is still the same woefulness .
        And why is it not possible to globally filter the db. instead I have to filter the tables, then filter the SPs, then filter the functions and so on. Why can’t the filter be added as a right-click on the database?

  2. I am disappointed that SSMS 18 does not have these Filter options. If you know a work-around please post it here.

    1. Correction: The ability to filter Database objects is not available if you are running SQL Server Express.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.