Data Services object list from local repository

[mageshwaran_subramanian]

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