**Using the profiler to capture the queries that runs when applying the `Contains` filter, I found the query:**
exec sp_executesql N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
serverproperty(N''Servername'')
AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
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 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'0'
Which contains the part:
and sp.name like N''%Test%''
And gives me the result:
Name Schema Urn CreateDate Owner IsEncrypted ImplementationType IsNativelyCompiled
TestCs dbo Server[@Name='DESKTOP-602CJ92\ 2019-01-28 20:05:28.220 dbo 0 1 0
As @BobKlimes noted, which means it is not possible on CS Servers.
It also appears to not be possible on CS DB's on CI Servers:
[![enter image description here][1]][1]
As a result, we have the same issue as you noted in the added [link][2].
Unless you find where the query is stored and change it, I don't think there is a way around it.
----------
Tried to add the collation hint using plan guides
-------------------------------------------------
The following query does give me what I want,as I added the COLLATE hint, so i tried creating a plan guide on the original query, with the collated query's query plan XML.
**Query that gives me the needed filtering**
exec sp_executesql N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
serverproperty(N''Servername'')
AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%''COLLATE Latin1_General_CI_AS)',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'0'
**Result:**
Name Schema Urn CreateDate Owner IsEncrypted ImplementationType IsNativelyCompiled
testCs dbo Server[@Name='DESKTOP-602CJ92\ 2019-01-28 20:05:23.830 dbo 0 1 0
TestCs dbo Server[@Name='DESKTOP-602CJ92\ 2019-01-28 20:05:28.220 dbo 0 1 0
Create the plan guide (XML not shown since too big) Followed this [Answer][3]
EXEC sp_create_plan_guide
@name = 'Filtering',
@stmt = N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
serverproperty(N''Servername'')
AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',
@type = 'SQL',
@params = N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',
@hints = @planXML
GO
**While I can create the plan guide, does not seem to stick to the original query.**
Commands completed successfully.
**No dice**
Name Schema Urn CreateDate Owner IsEncrypted ImplementationType IsNativelyCompiled
TestCs dbo Server[@Name='DESKTOP-602CJ92\ 2019-01-28 20:05:28.220 dbo 0 1 0
**Error found**
SELECT *
FROM sys.plan_guides
CROSS APPLY sys.fn_validate_plan_guide (plan_guide_id)
WHERE name = 'Filtering2'
[![enter image description here][4]][4]
**The same error happened when trying a different approach to adding the XML plan to a plan guide**
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text LIKE N'%uses_native_compilation%' and st.text like '%COLLATE%'
[![enter image description here][5]][5]
[1]: https://i.sstatic.net/PEwsy.png
[2]: https://stackoverflow.com/questions/1224364/sql-server-ignore-case-in-a-where-expression
[3]: https://dba.stackexchange.com/questions/130413/fixed-query-plan-for-a-stored-procedure
[4]: https://i.sstatic.net/orQn2.png
[5]: https://i.sstatic.net/oeo9D.png