Filtering Object Explorer

2

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.

FilteringObjectExplorer1

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.

FilteringObjectExplorer2

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

FilteringObjectExplorer3

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

FilteringObjectExplorer4

And hit OK to apply the filter.

FilteringObjectExplorer5

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.

FilteringObjectExplorer6

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.

2 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 […]

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 2,148 other followers

Follow me on Twitter

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