Sep 252012
 

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.

No filtering by role…

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.

  4 Responses to “Wanted: More Object Explorer Filters”

  1. Rather than extend what they have I think they should look at rewriting the filters like VS 2012 (http://msmvps.com/blogs/deborahk/archive/2012/09/02/solution-explorer-filtering-in-visual-studio-2012.aspx)
    A search box at the top and the tree rebuilds based on what you select (and as you type). I use VS a lot with SSDT projects and this is by far the quickest way I find objects.
    Also it searches all objects. Being that SSMS is now in the VS framework I really hope they make this happen.

    • Very cool! I don’t use Visual Studio very much these days so I didn’t know about that, but it looks like a great solution. Thanks for reading and pointing this out!

  2. I’d like to see more filters available. Like “Last Schema Change” or “Last Modified Date” for views to help me troubleshoot why things aren’t working. I don’t care when it was created, I care when it was messed up.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)