SQL Server Management Studio - using multiple filters in table list?

Question

In Management Studio, you can right click on the tables group to create a filter for the table list. Has anyone figured out a way to include multiple tables in the filter? For example, I'd like all tables with "br_*" and "tbl_*" to show up.

Anyone know how to do this?

Solution 1

You might be able to roll your own addon to SMSS that would allow you to do what you are looking for:

The Black Art of Writing a SQL Server Management Studio 2005 Add-In

Extend Functionality in SQL Server 2005 Management Studio with Add-ins

The first one is specifically for searching and displaying all schema objects with a given name so you might be able to expand upon that for what you are looking for.

Solution 2

No, you can't do this. When we first got Management Studio I've tried every possible combination of everything you could think of: _, %, *, ", ', &&, &, and, or, |, ||, etc...

Solution 3

I'm using SQL Server Management Studio v17.1 and it has a SQL injection bug in it's filter construction, so you can actually escape default

tbl.name like '%xxx%' 

and write your own query (with some limitations). For example to filter tables that are ending with "_arch", "_hist", "_purge" I used following filter value

_arch') and RIGHT(tbl.name, 5) != N'purge' and RIGHT(tbl.name, 4) != N'hist' and not(tbl.name like N'bbb

You can use SQL Server Profiler to see the constructed query and adjust it as needed.

Not sure if this same bug is available in previous SQL Management Studio versions or when it will be fixed, but for now I'm happy with the result.

Solution 4

At first it looks like it could use a CONTAINS query (e.g. "br_*" OR "tbl_*"), but it doesn't seem to. It seems to only support a value that is then passed into a LIKE clause (e.g. 'app' becomes '%app%').

Solution 5

I've used Toad for SQL Server (freeware version) which has very nice filtering options.

View more solutions

Comments

[Contango]

For such a popular piece of software with such huge licensing costs, SQL Management Studio suffers from some quite fundamental limitations. This is only the tip of the iceberg.

[vaitrafra]

Management Studio is a free software that comes along with SQL Server license.

[digital.aaron]

This seems to have been fixed as of 17.5. I can escape the filter and inject additional conditions without SSMS throwing an error, but it doesn't return any valid results.

[Albert]

I can't get this working in 18.5.1. The query ends up looking like: and dtb.name like N'%dbname1'''') OR (dtb.name LIKE ''''Adbname%')