March 9, 2016 at 5:05 am
I get following sql query is from Activity monitor, i am sure that it is not written by me. how it came and what it does ? can anybody explain to me. it takes more then 31000 ms
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],
case when 1=@is_policy_automation_enabled and 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],
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]
FROM
master.sys.databases AS dtb,
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
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((db_name()=@_msparam_4)and(dtb.name=db_name()))
ORDER BY
[Schema] ASC,[Name] ASC
March 9, 2016 at 6:08 am
I think that is an internal process from SQL Management Studio. It probably populates the tree structures in Object Explorer
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply