Wanted: More Object Explorer Filters

More often than not, I’ll expand the “Tables” folder in SQL Server Management Studio and browse the list to find whatever table or index I’m looking for. This works well in many situations, but when your database has over 20,000 tables, the results can be overwhelming! As your databases get larger and more complex, you may find yourself making use of features you previously didn’t need. For me, one of said features is filtering in SSMS object explorer.

Filters to the rescue

SSMS lets you create filters on certain parts of the object tree so you can see only what you’re interested in. In the case of tables, you can right-click on the “Tables” folder, choose Filter > Filter Settings, and then filter tables by Name, Schema, Owner, or Creation Date. In the images below you’ll see I’m filtering to only display tables whose schema name contains “Production”. To remove a filter, right-click on “Tables” again and go to Filter > Remove Filter.

Filters, filters, …everywhere??

Filtering can be helpful when you’re interested in looking at only a subset of tables or other objects, and they’re especially helpful when you’re working on systems with a huge number of objects and don’t want to scroll through an extremely long list. But unfortunately you can’t use them everywhere in the Object Explorer tree. I’ve never understood why – filtering is something simple enough for the client to handle. In the case of the example above, you can use SQL Server Profiler to see that filtering is done on the server, but I see little reason why it couldn’t take place on the client as well.

I went through the whole tree in SSMS 2008 and found that the following objects that can be filtered:

Database Level

• Database Diagrams
• Tables (and system tables)
• Views (and system views)
• Stored Procedures (and system stored procedures)
• Functions (Table-valued, Scalar-valued, Aggregate)
• Security (Users, Database Audit Specifications)

Server Level

• Security (Logins, Audits, Server Audit Specifications)
• Management (Policies, System Policies, Conditions, System Conditions)
• SQL Server Agent (Jobs)

It’s great that you can filter on all these things, but I think there’s a few more that should be filterable and aren’t, such as roles, schemas and types. I have a database with over 150 roles and 200 schemas – browsing those lists is a pain! I also got a chuckle when I found that database diagrams can be filtered. To me, that’s a seldom-used feature that totally doesn’t belong at the top of the tree (where I always click on it by accident) and then MS made it filterable for extra irony.

What about SSMS 2012? I didn’t go quite as in-depth as 2008, but it looks like everything is the same with respect to what can and cannot be filtered.

Like this idea?

If you think this makes sense, consider voting for this suggestion I just posted on MS Connect.