SSMS - How to do case-insensitive searches in Object Explorer

Preface: I am not referring to queries.

There are numerous places in SSMS that allow for filtering, such as the Object Explorer and Profiler, but these all treat filters as case-sensitive with no visible option otherwise, so if you search for contains 'ASDF' then values like "ASDF_MyEntity" are included but "asdf_MyEntity" are omitted.

For example, we have a lot of SQL Agent jobs on a large server, and I'm trying to use the Object Explorer to filter them by project name, which we always prefix the job names with. However, these appear as both uppercase and lowercase variants.

Another use case is searching for related stored procedure to a module among thousands of entries. If one is named inconsistently (e.g. PascalCase versus camelCase), the filtered search will omit it. This seems like quite an unnecessary hazard of phantoms for debugging.

Furthermore, the sorting in the Object Explorer places uppercase variants before lowercase (so uppercase Z comes before lowercase a) for every type of entity (e.g. table names, stored procedures, job names, etc.), so I either have to scroll around a lot, or check two different filters (or more if it's not all uppercase or all lowercase letters — 2len(name) times to be precise).

I realize I can just query jobs, tables, and other entities manually, but doing this is nonsensical given that SSMS exists, as its name is "SQL Server Management Studio."

Is there something I can do to make SSMS (and SQL Server Profiler) ignore case like every other Windows application? Perhaps I can change a collation setting locally?

Also, why was this decision made by Microsoft when implementing SSMS? I have found it to be only detrimental.

Furthermore, the loading splash screen for SSMS says "built on Visual Studio" which ignores case in Solution Explorer.

P.S. Using SSMS 2014 (version 12.0.5214.6). I cannot make any server-side changes, and my local development environment should match the target server environment for testing purposes

It is technically possible to do this case-insensitive filtering, though only due to a bug in SSMS. I don't have SSMS 2014 (version 12.x), but I did get it working in SSMS 17.9.1 and 18.0 Preview 6, connected to an instance with a default collation of Hebrew_100_BIN2.

Attempt Uno (no)

Seeing that the query uses LIKE, I figured I would try using single-character range wildcards (i.e. [ and ]). Passing in a pattern of [Tt][Ee][Ss][Tt] did not work, however, due to certain characters getting escaped. It seems that [, %, and _ all get wrapped in [ and ] to turn them into literals (i.e. no longer having special meaning).

Attempt Dos (Yes)

Seeing that the value used with the LIKE operator (i.e. the value entered into the UI) was concatenated into the query instead of passed in as a variable (i.e. tbl.name like N''%Test%'' instead of tbl.name like N''%'' + @_msparam_XX + N''%''), I started thinking, "Isn't there a reason why it's dangerous to do this with user input? Hmm, I wonder ..." And so I passed in a ' and it was escaped into ''. Normally this is exactly what should happen with a single-quote. However, this is being used in Dynamic SQL, so it's nested one-level deeper than where '' is properly escaped. A string within a string needs a single-quote to be escaped as '''', or double-escaped, because '' merely ends the string within a string.

For example, when filtering on "Tables", the generated code (the one line that matters) is:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Test%'')

If I had passed in "Te'st", the generated code would have been:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Te''st%'')

and yes, it does get an "unclosed quotation mark" error.

I tried adding a COLLATE clause, but all collations have underscores, so Latin1_General_100_CI_AS became Latin1[_]General[_]100[_]CI[_]AS. D'oh!

We can't pass in either [ or % without those being wrapped in [ and ], BUT, we can concatenate stuff onto the N'% string, such as the output of a function. And, we can bypass the escaping of the [ and % characters by passing them in as hex / VARBINARY values, and then CONVERT them back into NVARCHAR once inside.

So, when filtering on Stored Procedures, we are starting with normally generated code of:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1

In order to make this work, we need to pass in a string having the following structure (in place of "Test"):

' + CONVERT(NVARCHAR(MAX), 0x.....) AND 'x' <> '

We could use -- at the end instead of AND 'x' <> ', but the AND construct ensures that if there is additional parts of the query that following this part, and are on the same line, that it continues to function as expected.

To generate our "escaped" filter, we can use the following:

SELECT CONVERT(VARBINARY(MAX), N'[Tt][Ee][Ss][Tt]%'); -- must end with "%"
-- 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500

Pasting that hex value into our SQL Injection work-around, we get:

' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '

If we set our filter to the value directly above, the generated code will be:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%'' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND ''x'' <> ''%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'1'

And the generated code is passed into sp_executesql. The statement that is executed shows up in Profiler as:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N'%' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '%')

This technique works because 0xYYYY.... is not something that the parser is looking for. It's only looking for the literal values of [, _, and %, and we aren't passing those in. We are just passing in something that will be translated into those once the Dynamic SQL is executed.

But it doesn't need to be a VARBINARY string that is passed in. Any function that passes back a string will work. You just need to concatenate the ending of N'% with something, followed by what comes just before %'), hence: ' + {something_ending_with_%'} AND 'x' <> '. I could have used + NCHAR(91) + N'Tt' + NCHAR(93) + to represent [Tt], but that seemed a lot bulkier than a continuous VARBINARY literal. However, if you just want a %, then doing + NCHAR(37) + N' would be just fine. Remember to use NCHAR() and

StackExchange Database Administrators Q#228269, answer score: 8