SQL ServiceManager Class Properties and Relationships - CIRESON SENPAI NOTICE ME!
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