Home SQL Server

HOW TO - SCSM LIVE Database - Report FULL NAME of IR CLASSIFICATION

Conner_WoodConner_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

Sign In or Register to comment.