SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Filtering Object Explorer

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


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...