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.