SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_ENTITY_HIERARCHY]'))
DROP VIEW [dbo].[V_ENTITY_HIERARCHY]
GO

CREATE VIEW [dbo].[V_ENTITY_HIERARCHY]
AS
    WITH AllEntity 
    (EntityID, BaseEntityIndex, FullName, Depth) 
    AS
    (
        SELECT e.EntityID, e.EntityID BaseEntity, e.[Schema] + '.' + e.EntName, 0 Depth
        FROM VCLib.T_ENTITIES e
        UNION ALL 
        SELECT e.EntityID, e2.BaseEntityIndex, e.[Schema] + '.' + e.EntName, Depth + 1
        FROM VCLib.T_ENTITIES e
        inner join AllEntity e2 On e.BaseEntity = e2.EntityID
    )
    SELECT EntityID, BaseEntityIndex BaseEntity, FullName, Depth
    FROM AllEntity
GO
