CODEWITHSUNDEEP
sqlt-sqlrecursive-query
DNac
DNac

Reputation: 2783

T-SQL Recursive Query - Data Lineage

Given the following table

enter image description here

I need to get "data lineage" of the database objects.

The expected output (something like that):

enter image description here

This is the script I've tried.

CREATE TABLE #smth
     (
         ParObj   NVARCHAR(200)
         ,ChilObj NVARCHAR(200)
     );


INSERT INTO #smth (
    ParObj
    ,ChilObj
)
VALUES ( N'FactSales', N'qryFactSales' )
       ,( 'qryFactSales', 'qryFactSalesOnlineUnited' );

WITH cte
    AS (
           SELECT ParObj
                  ,ChilObj
                  ,level = 1
                  ,path = CAST('root' AS VARCHAR(100))
           FROM   #smth
           UNION ALL
           SELECT t2.ParObj
                  ,t2.ChilObj
                  ,level = t1.level + 1
                  ,Path = CAST(t1.path + ' ||| ' + CAST(t2.ChilObj AS VARCHAR(100)) AS VARCHAR(100))
           FROM   #smth AS t2
                  JOIN cte AS t1
                      ON t1.ChilObj = t2.ParObj
       )
SELECT   *
FROM     cte
ORDER BY cte.path;

Is it even achievable somehow?

Upvotes: 1

Views: 774

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This version does what you want:

with cte as (
      select parobj as obj, convert(nvarchar(max), NULL) as path
      from smth
      where not exists (select 1 from smth smth2 where smth2.chilobj = smth.parobj)
      union all
      select smth.chilobj as obj, convert(nvarchar(max), coalesce(path + ' -> ', '')) + cte.obj
      from cte join
           smth
           on cte.obj = smth.parobj
     )
select obj, coalesce(path + ' -> ' + obj, 'Root')
from cte;

Here is a db<>fiddle.

Upvotes: 2

Related Questions