Filtering Object Explorer


October 2, 2013 by Kenneth Fisher

I absolutely love learning new tricks that can be done in SSMS. Everything from opening initial connections to both the object explorer and a query window to changing the font that the query windows use. Recently I learned a new one while watching a SQL Skills Insider video. I’ve said this before and I’m going to say it again. If you aren’t a SQL Skills Insider you should be. In this particular case, among other things, they demonstrated filtering down what’s displayed in the object explorer

Here is an example. Adventureworks2008 has just over 70 tables. This isn’t a lot and we can certainly manage without filtering, but let’s pretend we have instead several thousand tables or maybe even tens of thousands. That can get a little bit more difficult to work with in Object Explorer.

Here is the base list of the AdventureWorks2008 tables.


So now let’s say we only want to see the tables in the Person schema. In fact let’s go a bit farther and say we only want the tables in the Person schema that have Person in the name.

First Right-Click on the Tables heading, select Filter and Filter Settings.


We now see that we can filter based on the Name, Schema, Owner and Creation Date of the object.


We change the Value field across from Name to ‘Person’ and the Schema Operator column to Equals and its Value to ‘Person’


And hit OK to apply the filter.


Now we only see the tables we are specifically interested in. Also note that the Tables heading specifically tells us that this is a ‘filtered’ list. This is a good thing since we might very well forget otherwise.

To get rid of the filter we have two options. One we can go back into the filter options and hit “Clear Filter” and OK again. Or right click on the Tables heading, select Filter and Remove Filter.


As best I can tell the filter option is available on most lists. A few exceptions include the database list, the Roles lists (database and server), Certificates etc. Of course this is using SQL 2008 R2 SSMS and may have changed as of SQL 2012 SSMS.

3 thoughts on “Filtering Object Explorer

  1. […] plan on going into in another post, but for now this has some great benefits. First of all you can filter the list just like in object explorer. So if you need to script just a portion of your 1000+ stored procedures you can filter the list […]

  2. […] not going to talk about using filters here for a couple of reasons. Firstly I’ve already talked about it but also because, unlike the other two options, it only affects the specific SSMS install where you […]

  3. […] not only makes it easy to manage the objects as you can filter down by schema or other criteria but also makes security management easier. For example you can grant user/role permissions at the […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: