Hello SQL Server developers and SQL Server database administrators,
I posted recently in my blog that I had successfully installed the Microsoft SQL Server 2008 February CTP aka Katmai CTP6 on my computer.
While working with MS SQL2008, I realized that the Object Explorer Filter fails to search and filter any database object (table, store procedure, view, etc.) on any version of the MS SQL Server database instances, SQL2005 or SQL Server 2008. The result set of the filter returns nothing.
When I run the SQL Server Profiler, I got the following query from the SQL Server traces.
The below query has been run on Microsoft SQL Server 2008 AdventureWorks sample database to filter stored procedures with names including "usp".
exec sp_executesql N'SELECT
sp.name AS [Name],
''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],
SCHEMA_NAME(sp.schema_id) AS [Schema],
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],
sp.create_date AS [CreateDate],
case when exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like ''Server'' + ''/Database\[@ID='' + convert(nvarchar(20),dtb.database_id) + ''\]''+ ''/StoredProcedure\[@ID='' + convert(nvarchar(20),sp.object_id) + ''\]%'' ESCAPE ''\'') then 1 else 0 end AS [PolicyHealthState]
FROM
master.sys.databases AS dtb,
sys.all_objects AS sp
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
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 sp.name like N''%@_msparam_4%'')and((db_name()=@_msparam_5)and(dtb.name=db_name()))
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'usp',@_msparam_5=N'AdventureWorks'
The execution of this sp_executesql t-sql command fails just because of the t-sql syntax of the sp.name comparison with @_msparam_4 parameter value using LIKE operator is built false.
sp.name like N''%@_msparam_4%''
must be altered with the following t-sql code statements
sp.name like ''%'' + @_msparam_4 + ''%''
I'm copying down here the correct form of the filter t-sql script that can be run on Microsoft SQL Server Management Studio Query Editor screens.
exec sp_executesql N'SELECT
sp.name AS [Name],
''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],
SCHEMA_NAME(sp.schema_id) AS [Schema],
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],
sp.create_date AS [CreateDate],
case when exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like ''Server'' + ''/Database\[@ID='' + convert(nvarchar(20),dtb.database_id) + ''\]''+ ''/StoredProcedure\[@ID='' + convert(nvarchar(20),sp.object_id) + ''\]%'' ESCAPE ''\'') then 1 else 0 end AS [PolicyHealthState]
FROM
master.sys.databases AS dtb,
sys.all_objects AS sp
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
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 sp.name like ''%'' + @_msparam_4 + ''%'')and((db_name()=@_msparam_5)and(dtb.name=db_name()))
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'usp',@_msparam_5=N'AdventureWorks'
A small bug in the SQL Server 2008 Management Studio CTP 6, but I believe this is a simple bug to resolve quickly.