IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'Microsoft_SystemCenter_Visualization_Library_ManagedEntityTypeRowIdValuesTableType' AND schema_id = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE TYPE SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityTypeRowIdValuesTableType AS TABLE ( ManagedEntityTypeRowId int )')
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'ManagedEntityBaseTypeHierarchyUsingTypeList') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION ManagedEntityBaseTypeHierarchyUsingTypeList() RETURNS @A TABLE (a int) AS BEGIN RETURN END')
GO
ALTER FUNCTION [dbo].[ManagedEntityBaseTypeHierarchyUsingTypeList]
(
@ManagedEntityTypeRowIdTVP SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityTypeRowIdValuesTableType READONLY,
@LevelCount int = 0
)
RETURNS @ResultTable TABLE
(
[Level] int NOT NULL
,ManagedEntityTypeRowId int NOT NULL
)
AS
BEGIN
DECLARE
@CurrentLevel int
,@RowCount int
SET @CurrentLevel = 1
SET @RowCount = 1
INSERT @ResultTable ([Level], ManagedEntityTypeRowId)
SELECT 0, ManagedEntityTypeRowId
FROM @ManagedEntityTypeRowIdTVP
WHILE (((@LevelCount >= @CurrentLevel) OR (@LevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT @ResultTable ([Level], ManagedEntityTypeRowId)
SELECT DISTINCT @CurrentLevel, BaseManagedEntityTypeRowId
FROM vManagedEntityTypeManagementPackVersion metmpv
JOIN @ResultTable rt ON (metmpv.ManagedEntityTypeRowId = rt.ManagedEntityTypeRowId)
WHERE (metmpv.BaseManagedEntityTypeRowId IS NOT NULL)
AND (rt.[Level] = @CurrentLevel - 1)
SET @RowCount = @@ROWCOUNT
SET @CurrentLevel = @CurrentLevel + 1
END
RETURN
END
GO
GRANT SELECT ON ManagedEntityBaseTypeHierarchyUsingTypeList TO OpsMgrReader
GRANT EXECUTE ON TYPE::[SDK].[Microsoft_SystemCenter_Visualization_Library_ManagedEntityTypeRowIdValuesTableType] TO OpsMgrReader
GO
Home