SQL Server: how to get data from lowest hierarchy in hierarchy table?

I have a hierarchical table like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EX_TABLE](
    [PARENT_OBJ] [nvarchar](255) NOT NULL,
    [PARENT_OBJ_TYPE] [nvarchar](64) NOT NULL,
    [DESCEN_OBJ] [nvarchar](255) NOT NULL,
    [DESCEN_OBJ_TYPE] [nvarchar](64) NOT NULL,
    [DESCEN_OBJ_USAGE] [nvarchar](20) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'batch_name', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'print', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'batch_run_id', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'db_name', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'repo_name', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'WF_Batch_name_1', N'WF', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'Batch_name_1', N'Batch', N'table_attr', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'WF_Batch_name_1', N'WF', N'DF_Batch_name_1', N'DF', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'WF_Batch_name_1', N'WF', N'DF_Batch_name_1_2', N'DF', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'TABLE_1', N'Table', N'Source')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'Query', N'Transform', N'Transform')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'sysdate', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'TABLE_2', N'Table', N'Target')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'TABLE_2', N'Table', N'Key')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'DS_NAME', N'Ds', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1', N'DF', N'Key', N'Trans', N'Trans')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1_2', N'DF', N'TABLE_1', N'Table', N'Source')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_B开发者_StackOverflow社区atch_name_1_2', N'DF', N'sysdate', N'Built In Function', NULL)
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1_2', N'DF', N'TABLE_3', N'Table', N'Target')
INSERT [dbo].[EX_TABLE] ([PARENT_OBJ], [PARENT_OBJ_TYPE], [DESCEN_OBJ], [DESCEN_OBJ_TYPE], [DESCEN_OBJ_USAGE]) VALUES (N'DF_Batch_name_1_2', N'DF', N'Key', N'Trans', N'Trans')

The idea is to traverse tree upwards from DF to WF and to Batch. In this example, Batch_name_1 has a many child objects (DESCEN_OBJ) but only WF child is interesting (WF_Batch_name_1). WF_Batch_name_1 has also child objects (DESCEN_OBJ), which are DF objects (DF_Batch_name_1, DF_Batch_name_1_2).

DF_Batch_name_1 and DF_Batch_name_1_2 have also child objects, but I'm only interested in table objects (from DESCEN_OBJ_TYPE) which have DESCEN_OBJ_USAGE as Target.

Batch is always the highest member in this tree and DF the lowest, but there can bee other members between these two. See that this is only the subset of real data.

So, how can I query distinct number of target (DESCEN_OBJ_USAGE) tables (DESCEN_OBJ_TYPE) for Batch_name_1 (PARENT_OBJ). The result should be 2 in this case (with real data this does not produce right result when I have many batches (highest member in hierarchy), so traversing upwards to batches is needed):

SELECT COUNT(distinct descen_obj) as dobj FROM EX_TABLE
WHERE DESCEN_OBJ_TYPE = 'Table' and DESCEN_OBJ_USAGE = 'Target'

Hierarchy is represented in DESCEN_OBJ field. Can this be achieved using single query (recursive CTE?) without temporary tables? Any advice regarding this would be most appreciated!

I am not sure I understand your question, but might you be looking for this?

with tree as (
   select parent_obj,
          parent_obj_type,
          descen_obj, 
          descen_obj_usage,
          descen_obj_type,
          1 as level
   from ex_table
   where parent_obj = 'Batch_name_1'

   union all

   select e.parent_obj,
          e.parent_obj_type,
          e.descen_obj,
          e.descen_obj_usage,
          e.descen_obj_type,
          t.level + 1
   from ex_table e
      join tree t on e.parent_obj = t.descen_obj
) 
select *
from tree
where descen_obj_type = 'Table' 
  and descen_obj_usage = 'Target'

It would be very helpful if you could post the expected output from your sample data and a more detailed explanation on how that should be achieved.

If you want to get all DF-children of Batch_name_1 and these can be identified through [PARENT_OBJ_TYPE] = 'DF', and there are an unknown number of layers/members between then I might be suggesting using some temporary tables like this:

-- Creating a temporary table where we will store all found members from the lowest level
CREATE TABLE #DF (
    [DESCEN_OBJ_TYPE] [nvarchar](64) NOT NULL,
    [DESCEN_OBJ_USAGE] [nvarchar](20) NULL
)

CREATE TABLE #DESCEN (
    [DESCEN_OBJ] [nvarchar](255) NOT NULL
)

-- First we get the initial top layer
SELECT *
INTO #PARENTS
FROM [dbo].[EX_TABLE]
WHERE [PARENT_OBJ] = 'Batch_name_1'

-- Loop running as long as there are children
WHILE EXISTS (SELECT * FROM #PARENTS)
BEGIN

    -- Storing away the DF levelled members
    INSERT INTO #DF ([DESCEN_OBJ_TYPE],[DESCEN_OBJ_USAGE])
    SELECT [DESCEN_OBJ_TYPE],[DESCEN_OBJ_USAGE]
    FROM #PARENTS WHERE [PARENT_OBJ_TYPE] = 'DF'

    INSERT INTO #DESCEN ([DESCEN_OBJ])
    SELECT DISTINCT [DESCEN_OBJ]
    FROM #PARENT WHERE [PARENT_OBJ_TYPE] <> 'DF'

    -- Clearing the parents table since we are going to fill it with the next layer
    TRUNCATE TABLE #PARENTS
    INSERT INTO #PARENTS
    SELECT ex.*
    FROM #DESCEN de
    INNER JOIN [dbo].[EX_TABLE] ex ON ex.[PARENT_OBJ] = de.[DESCEN_OBJ]

    TRUNCATE TABLE #DESCEN

END

-- Finally outputting
SELECT DISTINCT * FROM #DF

DROP TABLE #PARENTS
DROP TABLE #DESCEN
DROP TABLE #DF

I might have misunderstood your problem. And also, if there is a set number of layers you can probably make it in a much simpler manner.

I haven't tested the code and there might be some bug in it, but I hope you understand the concept.

Also, be careful. If you have a hierarchy loop (a decendant having a higher level member as child), this will be stuck in an eternal loop.

Is this what you're talking about?

-- flatten hierarchy while keeping top-level ID
WITH all_descendants(top_parent_obj, middle_obj, descen_obj) AS (
    SELECT parent_obj, descen_obj, descen_obj
      FROM dbo.EX_TABLE
     WHERE PARENT_OBJ_TYPE = 'Batch'
     UNION ALL
    SELECT p.top_parent_obj, c.DESCEN_OBJ, c.DESCEN_OBJ
      FROM all_descendants p
     INNER JOIN dbo.EX_TABLE c
             ON p.middle_obj = c.PARENT_OBJ
)
-- show distinct usages by top-level ID
SELECT d.top_parent_obj, o.DESCEN_OBJ_USAGE
  FROM all_descendants d
 INNER JOIN dbo.EX_TABLE o
         ON d.descen_obj = o.DESCEN_OBJ
 GROUP BY d.top_parent_obj, o.DESCEN_OBJ_USAGE

The output is:

Batch_name_1    NULL
Batch_name_1    Key
Batch_name_1    Source
Batch_name_1    Target
Batch_name_1    Trans
Batch_name_1    Transform