Skip to Content

Data Services object list from local repository

Here’s the t-sql query which gives the list of jobs, work flows,data flows and tables used within a local repository.

This has been tested in Data Services 4.2 with SQL server 2008.This query won’t list the nested data flows and the data flows not used within a work flow.


SELECT
j.Job
,w.WF_Name
,isnull(d. DF_Name,'') as DF_Name
,isnull(t.Table_Name,'') as Table_Name
,isnull(t.DESCEN_OBJ_USAGE,'') as Table_usage
,isnull(t.DESCEN_OBJ_OWNER,'') as Table_Owner
FROM
(
  SELECT  distinct PARENT_OBJ as job  FROM ALVW_PARENT_CHILD
  where parent_OBJ_TYPE = 'JOB'
) j
left join
(
  SELECT  distinct PARENT_OBJ as job , DESCEN_OBJ as WF_Name FROM ALVW_PARENT_CHILD
  where DESCEN_OBJ_TYPE = 'Workflow' and  PARENT_OBJ_type = 'JOB'
  union
  SELECT  distinct b.PARENT_OBJ as job , a.DESCEN_OBJ as WF_Name FROM ALVW_PARENT_CHILD a
  join ALVW_PARENT_CHILD b
  on a.PARENT_OBJ_TYPE = 'Workflow' and a.DESCEN_OBJ_TYPE = 'Workflow'
  and a.PARENT_OBJ = b.DESCEN_OBJ
  and b.PARENT_OBJ_TYPE = 'Job'
)w
on j.job = w.job
left join
(
  SELECT  distinct PARENT_OBJ as WF_Name , DESCEN_OBJ as DF_Name FROM ALVW_PARENT_CHILD
  where PARENT_OBJ_TYPE = 'Workflow' and DESCEN_OBJ_TYPE = 'Dataflow'
)d on w.WF_Name = d.WF_Name
left join
(
  SELECT  distinct PARENT_OBJ as DF_Name , DESCEN_OBJ as Table_Name ,DESCEN_OBJ_OWNER,DESCEN_OBJ_USAGE FROM ALVW_PARENT_CHILD
  where PARENT_OBJ_TYPE = 'Dataflow' and DESCEN_OBJ_TYPE = 'table'
)t on d.DF_Name = t.DF_Name
order by j.Job,w.WF_Name,d. DF_Name,t.Table_Name

If there’s a simpler query to achieve this or if it doesn’t work for your local repository,please share your thoughts in the comment section.

se          sdflec

2 Comments
You must be Logged on to comment or reply to a post.