Skip to main content
added clarification and links
Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300
  1. Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. It might be a good ideaPlease see the "UPDATE" section at the bottom for links to add athe bug report I filed about this, as well as the enhancement suggestion over at https://feedback.azure.com/forums/908035-sql-serverI filed requesting a "case-insensitive" check-box on the "Filter Settings" dialog :-), per filterable property.

  2. From Randi's answer:

Well, it depends. There are two levels of collations — instance-level and database-level — and the one that matters depends on what meta-data someone is trying to filter. Instance-level collation affects filtering instance-level meta data (e.g. Logins, Linked Servers, etc) and meta data in system databases. Database-level collation affects filtering user database meta data (e.g. Users, Tables, etc). In a Contained / Partially-Contained database, filtering the database-level meta data will always use the collation: Latin1_General_100_CI_AS_WS_KS_SC.

UPDATE

I have submitted the following feedback items to Microsoft:

  1. BUG: SSMS: Object Explorer Filtering allows for SQL Injection (oops)
  2. SUGGESTION: SSMS: Allow forcing case-insensitive matching in Object Explorer filters
  1. Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. It might be a good idea to add a suggestion over at https://feedback.azure.com/forums/908035-sql-server requesting a "case-insensitive" check-box on the "Filter Settings" dialog :-).

  2. From Randi's answer:

Well, it depends. There are two levels of collations — instance-level and database-level — and the one that matters depends on what meta-data someone is trying to filter. Instance-level collation affects filtering instance-level meta data and meta data in system databases. Database-level collation affects filtering user database meta data. In a Contained / Partially-Contained database, filtering the database meta data will always use the collation: Latin1_General_100_CI_AS_WS_KS_SC.

  1. Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. Please see the "UPDATE" section at the bottom for links to the bug report I filed about this, as well as the enhancement suggestion I filed requesting a "case-insensitive" check-box, per filterable property.

  2. From Randi's answer:

Well, it depends. There are two levels of collations — instance-level and database-level — and the one that matters depends on what meta-data someone is trying to filter. Instance-level collation affects filtering instance-level meta data (e.g. Logins, Linked Servers, etc) and meta data in system databases. Database-level collation affects filtering user database meta data (e.g. Users, Tables, etc). In a Contained / Partially-Contained database, filtering the database-level meta data will always use the collation: Latin1_General_100_CI_AS_WS_KS_SC.

UPDATE

I have submitted the following feedback items to Microsoft:

  1. BUG: SSMS: Object Explorer Filtering allows for SQL Injection (oops)
  2. SUGGESTION: SSMS: Allow forcing case-insensitive matching in Object Explorer filters
added note about this technique not being guaranteed to work forever and to formally request a "case-insensitive" option
Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300
  1. From Randi's answer:

    Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. It might be a good idea to add a suggestion over at https://feedback.azure.com/forums/908035-sql-server requesting a "case-insensitive" check-box on the "Filter Settings" dialog :-).

  2. From Randi's answer:

  1. From Randi's answer:
  1. Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. It might be a good idea to add a suggestion over at https://feedback.azure.com/forums/908035-sql-server requesting a "case-insensitive" check-box on the "Filter Settings" dialog :-).

  2. From Randi's answer:

added clarification
Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300

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 prefix literals with N since this is all NVARCHAR data.

USE [tempdb];
IF (OBJECT_ID(N'dbo.ObjectFilter') IS NOT NULL)
BEGIN
  DROP FUNCTION dbo.ObjectFilter;
END;

GO
CREATE FUNCTION dbo.ObjectFilter(@Name [sysname], @Filter [sysname])
RETURNS BIT
AS
BEGIN
  IF (@Name LIKE N'%' + @Filter + N'%' COLLATE Latin1_General_100_CI_AS_SC)
  BEGIN
    RETURN 1;
  END;

  RETURN 0;  
END;
GO

And, this function will stick around until the instance is restarted. So you will only need to execute the above T-SQL every once in a while.

USE [tempdb];
GO
CREATE FUNCTION dbo.ObjectFilter(@Name [sysname], @Filter [sysname])
RETURNS BIT
AS
BEGIN
  IF (@Name LIKE N'%' + @Filter + N'%' COLLATE Latin1_General_100_CI_AS_SC)
  BEGIN
    RETURN 1;
  END;

  RETURN 0;  
END;
GO

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 prefix literals with N since this is all NVARCHAR data.

USE [tempdb];
IF (OBJECT_ID(N'dbo.ObjectFilter') IS NOT NULL)
BEGIN
  DROP FUNCTION dbo.ObjectFilter;
END;

GO
CREATE FUNCTION dbo.ObjectFilter(@Name [sysname], @Filter [sysname])
RETURNS BIT
AS
BEGIN
  IF (@Name LIKE N'%' + @Filter + N'%' COLLATE Latin1_General_100_CI_AS_SC)
  BEGIN
    RETURN 1;
  END;

  RETURN 0;  
END;
GO

And, this function will stick around until the instance is restarted. So you will only need to execute the above T-SQL every once in a while.

added final generated code
Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300
Loading
Source Link
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300
Loading