Skip to Content
Author's profile photo Magesh Subramanian

List of Information Steward tasks and assoicated objects

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
 ,t4.technical_name as Table_name
 ,t6.technical_name as Task_name
 ,t6.transformation_package_type_cd as Task_type
       select  distinct
        t1.technical_name as Project_name
       ,t3.technical_name as connection_name
       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
 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.

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Alejandro Morcillo
      Alejandro Morcillo

      Hi Magesh,

      The query you have written has been very helpful to me. I have been trying to modify it to adapt it to my needs but I could not. Do you know if it would be possible to extract all the tables and views of a project? I mean, in this query only shows the tables associated with a TASK. I would to know if it's possible to show all the tables and views associated to a IS projects even if you don't use it in a Task.

      Thanks so much.