asjZMzdZ

11    object1    0  
22    object2    1  
33    object3    1  
44    object4    0  
55    object5    3  
66    object6    2  
77    object7    3  
88    object8    5
9	
10DROP TABLE IF EXISTS dbo.ObjectTree;
11
12CREATE TABLE dbo.ObjectTree
13(
14    [object_id] integer NOT NULL
15        CONSTRAINT [PK dbo.ObjectTree object_id]
16        PRIMARY KEY CLUSTERED,
17    [object_name] varchar(50) NOT NULL
18        CONSTRAINT [UQ dbo.ObjectTree object_name]
19        UNIQUE NONCLUSTERED,
20    derived_from_object_id integer NULL
21        CONSTRAINT [FK object_id]
22        FOREIGN KEY (derived_from_object_id)
23        REFERENCES dbo.ObjectTree ([object_id]),
24
25    INDEX [IX dbo.ObjectTree derived_from_object_id] (derived_from_object_id)
26);
27
28INSERT dbo.ObjectTree
29    ([object_id], [object_name], derived_from_object_id)
30VALUES
31    (1, 'object1', NULL),  
32    (2, 'object2', 1),  
33    (3, 'object3', 1),  
34    (4, 'object4', NULL),  
35    (5, 'object5', 3),  
36    (6, 'object6', 2),  
37    (7, 'object7', 3),  
38    (8, 'object8', 5);
39	
40-- Where to start
41DECLARE @ObjectID integer = 1;
42
43WITH R AS
44(
45    -- Anchor
46    SELECT
47        OT.[object_id]
48    FROM dbo.ObjectTree AS OT
49    WHERE
50        OT.[object_id] = @ObjectID
51
52    UNION ALL
53
54    -- Recursive
55    SELECT
56        OT.[object_id]
57    FROM R
58    JOIN dbo.ObjectTree AS OT
59        ON OT.derived_from_object_id = R.[object_id]
60) 
61SELECT
62    derivations = COUNT_BIG(*)
63FROM R;