Query to List all hierarchical children with parent, grandparent etc. from entire table
you just cannot do recursive on millions of rows.you have to put some filter

Try this,

F object_id('tempdb..#table') IS NOT NULL
    DROP TABLE #table

CREATE TABLE #table (
    id INT identity(1, 1)
    ,[Name] VARCHAR(16)
    ,ParentID INT NULL
    )

INSERT INTO #table
VALUES('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

    WITH CTE
AS (
    SELECT id
        ,ParentID,1 DirectP
    FROM #table

    UNION ALL

    SELECT r.ID
        ,t.parentID,0 DirectP
    FROM #table t
    INNER JOIN CTE r ON t.id = r.parentID
    WHERE t.ParentID IS NOT NULL
    )
SELECT r.ID
    ,r.ParentID
   ,DirectP
FROM CTE r

ORDER BY id
    ,ParentID DESC