HOW TO - SCSM LIVE Database - Report FULL NAME of IR CLASSIFICATION
Conner_Wood
Customer Ninja IT Monkey ✭✭✭✭
Looks like I finally had to do it for the LIVE Database, which was a lot harder due to there not being a dedicated table to get the correct list of Enums.
Also I do not like the new Cireson way of making a code block on these forums.
Below is the SQL required to generate "IRClassification_EnumFullName" which contains the FULL PATH/FULL NAME of the Incident Classification Enumeration minus the Root Enumeration to make it more readable.
WITH CTE_IRClassification AS
(
SELECT ParentEnum.EnumTypeName, ParentEnum.EnumTypeId, ParentEnum.ParentEnumTypeId, CAST(NULL as nvarchar(256)) AS 'ParentEnumTypeName'
FROM [dbo].[EnumType] ParentEnum (nolock)
WHERE ParentEnum.EnumTypeName = 'IncidentClassificationEnum'
UNION ALL
SELECT Enums.EnumTypeName, Enums.EnumTypeId, Enums.ParentEnumTypeId, ParentEnum.[EnumTypeName] AS 'ParentEnumTypeName'
FROM [dbo].[EnumType] Enums (nolock)
JOIN [dbo].[EnumType] ParentEnum (nolock)
ON ParentEnum.EnumTypeId = Enums.[ParentEnumTypeId]
JOIN CTE_IRClassification ON Enums.ParentEnumTypeId = CTE_IRClassification.EnumTypeId
)
,IRClassification_EnumName AS
(
SELECT Enum.[EnumTypeId]
,[Enum].[EnumTypeName]
,[Enum].[ParentEnumTypeId]
,[Enum].[ParentEnumTypeName]
,[LT].[LTValue] AS 'EnumDisplayName'
FROM CTE_IRClassification Enum
JOIN [ServiceManager].[dbo].[LocalizedText] LT (nolock)
ON [LT].[LanguageCode] = 'ENU'
AND [LT].[LTStringType] = 1 -- 1 is localized DisplayName for the element, 2 is Description
AND [LT].[LTStringId] = Enum.[EnumTypeId]
)
,IRClassification_EnumFullName AS
(
SELECT Enum.[EnumTypeId]
,[Enum].[EnumTypeName]
,cast(Enum.[EnumDisplayName] as nvarchar(max)) AS 'EnumDisplayName'
,Enum.[ParentEnumTypeId]
,Enum.[ParentEnumTypeName]
FROM IRClassification_EnumName Enum
WHERE Enum.[ParentEnumTypeId] IS NULL
UNION ALL
SELECT Enum.[EnumTypeId]
,[Enum].[EnumTypeName]
,IIF(Enum.[ParentEnumTypeName] = 'IncidentClassificationEnum' OR Enum.[EnumTypeName] = 'IncidentClassificationEnum', cast(Enum.[EnumDisplayName] as nvarchar(max)), cast(FullEnum.[EnumDisplayName] + '\' + Enum.[EnumDisplayName] as nvarchar(max))) AS 'EnumDisplayName'
,Enum.[ParentEnumTypeId]
,Enum.[ParentEnumTypeName]
FROM IRClassification_EnumName Enum
JOIN IRClassification_EnumFullName FullEnum on Enum.[ParentEnumTypeId] = FullEnum.[EnumTypeId]
)
--SELECT * FROM CTE_IRClassification
--SELECT * FROM IRClassification_EnumName
SELECT * FROM IRClassification_EnumFullName
OPTION(MAXRECURSION 10) -- SET TO 0 IF YOU WANT UNLIMITED RECURSION/CHILD DEPTH