I have been playing around with SQL Server 2016 for a while now and it is interesting how some of the things have changed drastically with this release. One of the key things I felt was the ability to have a different cycle for SQL Server Management Studio (a.k.a SSMS) enhancements. Since this is a client tool, I see that Microsoft has gone ahead with updating the tool almost every month now. Let us learn about SSMS Enhancement.
I go to customer locations and I see all sorts of weird things happening at their end when I see a DBA working on his server. In a recent conversation, I had been to a customer who had close to 86 databases on his server. It was a mayhem if they had to go to one of the databases. And SQL Server Management Studio didn’t have the ability to filter the list either.
When I was playing with the latest release of SSMS, I figured out this has been added to the toolset. This was an amazing feeling because I had recently been in a customer environment and it was a productivity issue when such capabilities go missing.
To simulate the new capability, check the Filter button when on Databases and also on the right click “Filter” -> “Filter Settings”. These will now be enabled at the database node too.
Once you click this, we will be presented by yet another screen which is quite familiar to folks who have worked with SSMS before. This is the place where we enter the values to filter.
Once this is done and we press the “OK” button. The databases node will be filtered automatically.
In the above example, we will get all the database with “Adventure” in the name. This in my opinion is a little addition to the tool but a powerful addition to something that we have been wanting for years now.
Do let me know if you see this being used in your environments? What is the maximum number of databases you have on a single server? Do let me know via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Great Enhancement. Thank Pinal for sharing
Very Useful Enhancement. Thanks Pinal for sharing
I thought this would be useful, but the filter is removed when you go out of SSMS. I’m unable to figure out how to make the filter permanent. So, basically, it’s just a simple search utility.
We have 25 databases, but there are only 3 databases that we routinely use. Eventually, we will move most of the databases to a different server which is a better solution anyway.
My work server list is in the hundreds of databases, but I only use 4-6 at a time. How can I filter for 4 of them. Can I use some type of “OR” in my filter? i.e. “A” or “B” or “K” or “Z”
i don’t know way to put multiple filters.