Informatica data modeling Constraints for a Table Report

Lists constraints, such as Unique Key, Primary Key, Foreign Key, and Rule constraints, by the table for which the constraint is defined. It also provides the columns that participate in each constraint.


Click on the text to copy the query report to your clipboard.


select "LX_LOCATION",
"MODEL_NAME",
"TABLE_NAME",
"REPOSITORY_NAME",
"CONSTRAINT_TYPE",
"COLUMN_NAME",
"CONSTRAINT_NAME"
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME as REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME as MODEL_NAME, ODS_CHILD_LVL2_ELMT.ELEMENT_NAME as CONSTRAINT_NAME , ODS_CHILD_LVL2_ELMT.CLASS_TYPE as CONSTRAINT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME as TABLE_NAME , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME as COLUMN_NAME , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ASSOC ODS_CHILD_LVL2_ASOC, IMA_ELEMENT ODS_CHILD_LVL2_ELMT, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELMNT_ATTR ODS_CHILD_LVL2_ATTR, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ASOC.TO_ELEMENT_UID = ODS_CHILD_LVL2_ELMT.ELEMENT_UID AND ODS_CHILD_LVL2_ASOC.FROM_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ATTR.ELEMENT_UID = ODS_CHILD_LVL2_ELMT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = 'Data Modeling' AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE = 'Model' AND ODS_IMA_ELEMENT.CLASS_TYPE = 'Table' AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = 'name' AND ODS_CHILD_LVL2_ELMT.CLASS_TYPE IN ('ForeignKey', 'PrimaryKey', 'UniqueKey', 'RuleConstraint') AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = 'Column' AND ODS_CHILD_LVL2_ATTR.CLASS_ATTR_NAME = 'name' AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE '%N%' )) "query"
order by "REPOSITORY_NAME", "MODEL_NAME", "CONSTRAINT_NAME", "CONSTRAINT_TYPE", "TABLE_NAME"

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
*
*