List of Information Steward tasks and assoicated objects

[mageshwaran_subramanian]

Here's a SQL query which lists the Information Steward projects , Connections , tables and the tasks (profile/rule) associated.This query access the Information Steward metadata tables available in Information Steward repository.The following query is developed and tested with SQL server 2012 and Information Steward 4.2.You can tweak the query little bit to be able to work with other Database like Oracle , MySQL , DB2 et al.

 select distinct

 r1.Project_name

 ,r1.Connection_name

 ,t4.technical_name as Table_name

 ,t6.technical_name as Task_name

 ,t6.transformation_package_type_cd as Task_type

 from

 (

       select  distinct

        t1.technical_name as Project_name

       ,t1.configuration_id

       ,t3.technical_name as connection_name

       ,t3.connection_id,t2.[object_id]

       ,t2.related_object_id

       from

       MMT_Data_Group t1 

       join MMT_relationship t2 on t1.configuration_id = t2.configuration_id and t2.is_current_version = 'Y' and t1.data_group_type_cd = 'PPRJ'

       join MMT_Connection t3 on t2.user_bigint = t3.connection_id and t3.is_current_version = 'Y'  and t3.sub_type is not null

 )r1 

 join MMT_Data_Group t4 on t4.connection_id = r1.connection_id and t4.data_group_id = r1.related_object_id and t4.is_current_version = 'Y'  and t4.data_group_type_cd in ( 'RTBL' ,'VVIW')

 join MMT_relationship t5 on r1.related_object_id = t5.related_object_id and t5.is_current_version = 'Y' and  r1.configuration_id = t5.configuration_id

 join MMT_transformation_package t6 on t5.[object_id] = t6.transformation_package_id and t6.is_current_version = 'Y'

 where isnull(t6.transformation_package_type_cd,'') <> 'JOB'

If you find a simpler query to achive the same result , please share it in the comment section.