Filtering Tables, Procedures, And Other Objects In SSMS

, 2018-07-05 (first published: )

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads