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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, SSMS