SQL ServiceManager Class Properties and Relationships - CIRESON SENPAI NOTICE ME!

Conner_Wood

Cireson, the community wants you to make it better, make the search better and dynamically get the properties and relationships of the class you are targeting!

Here is your headstart for Class Properties:

WITH cte_Children AS 
(
  SELECT a.[ManagedTypeId], a.[BaseManagedTypeId]
  FROM [dbo].[ManagedType] a
  WHERE a.TypeName = 'System.WorkItem.Incident'
  UNION ALL -- Get Children
  SELECT a.[ManagedTypeId], a.[BaseManagedTypeId]
  FROM [dbo].[ManagedType] a 
  JOIN cte_Children c 
  ON a.[BaseManagedTypeId] = c.[ManagedTypeId]
  AND a.[IsExtensionType] = 1
)
,cte_Parents AS
(
  SELECT a.[ManagedTypeId], a.[BaseManagedTypeId]
  FROM [dbo].[ManagedType] a
  WHERE a.TypeName = 'System.WorkItem.Incident'
  UNION ALL
  SELECT c.[ManagedTypeId], c.[BaseManagedTypeId]
  FROM [dbo].[ManagedType] c (nolock)
  JOIN cte_Parents p
  ON p.[BaseManagedTypeId] = c.[ManagedTypeId]  -- this is the recursion to get parent of child.
)
,cte_ClassFilter AS
(
SELECT [BaseManagedTypeId], [ManagedTypeId]
FROM cte_Parents
UNION ALL
SELECT [BaseManagedTypeId], [ManagedTypeId]
FROM cte_Children
)
SELECT ClassProp.[ManagedTypePropertyId]
	,ClassProp.[ManagedTypePropertyName]
	,Class.[TypeName]
	,Class.[ManagedTypeViewName]
	,ClassProp.[ColumnName]
FROM [dbo].[ManagedTypeProperty] ClassProp (nolock)

JOIN [dbo].[ManagedType] Class (nolock)
ON Class.[ManagedTypeId] = ClassProp.[ManagedTypeId]
AND Class.ManagedTypeId IN 
(
  SELECT ManagedTypeId 
  FROM cte_ClassFilter
)

The above query gives you back all the class properties that the Incident can save!

Here is your headstart for Class Relationships:

USE [ServiceManager];

WITH CTE_Class AS
(
  SELECT MT.[ManagedTypeId], MT.[BaseManagedTypeId]
  FROM [dbo].[ManagedType] MT (nolock)
  WHERE MT.[TypeName] = 'System.WorkItem.Incident' -- This is the class you want to see all the relationships it can be a part of.
  UNION ALL
  SELECT c.[ManagedTypeId], c.[BaseManagedTypeId]
  FROM [dbo].[ManagedType] c (nolock)
  JOIN CTE_Class p
  ON p.[BaseManagedTypeId] = c.[ManagedTypeId]  -- this is the recursion to get parent of child.
)
,Class_Relationships AS
(
  SELECT [RelationshipTypeId]
  FROM [dbo].[RelationshipType] RT (nolock)

  JOIN CTE_Class SourceFilter
  ON SourceFilter.[ManagedTypeId] = RT.[SourceManagedTypeId]

  UNION --Just UNION removes Duplicates

  SELECT [RelationshipTypeId]
  FROM [dbo].[RelationshipType] RT (nolock)

  JOIN CTE_Class TargetFilter
  ON TargetFilter.[ManagedTypeId] = RT.[TargetManagedTypeId]
)
SELECT RT.[RelationshipTypeId]
      ,RT.[RelationshipTypeName]
FROM Class_Relationships

JOIN [dbo].[RelationshipType] RT (nolock)
ON RT.[RelationshipTypeId] = Class_Relationships.[RelationshipTypeId]

The above query returns only relationship classes that an incident is able to be related to (though you would have to determine if you should relate to source or target).



#UlteriorMotive#UlteriorMotive#UlteriorMotive#UlteriorMotive#UlteriorMotive#UlteriorMotive