Informatica data modeling Table and Views Details report

Column Level

Lists tables by Model, and table column information, such as name, precision, scale, and datatype.

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


select "L2_PRECISION",
"OBJECT_DESCRIPTION",
"REPOSITORY_NAME",
"MODEL_NAME",
"LX_LOCATION",
"IS_NULL_INDICATOR",
"L2_SCALE",
"OBJECTTYPE",
"COLUMN_NAME",
"OBJECT_NAM",
"COLUMN_DESCRIPTION",
"L2_DATATYPE"
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS MODEL_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = 'infa_type' AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS OBJECTTYPE , ODS_IMA_ELEMENT.ELEMENT_NAME AS OBJECT_NAM, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = 'infa_description' AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS Object_DESCRIPTION , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS COLUMN_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = 'infa_description' AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS Column_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME IN ('dataType', 'datatype', 'Data Type', 'DataType', 'Datatype') AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_DATATYPE , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = 'precision' AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_PRECISION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = 'scale' AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_SCALE , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = 'isNullable' AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS IS_NULL_INDICATOR , 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_ATTR ODS_IMA_ELMNT_ATTR, IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, 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_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_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 IN ('Table', 'View') AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = 'Column' AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = 'name' AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = 'name' AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE '%N%' )) "query"
where "REPOSITORY_NAME" in ('<Repository_Name>')
order by "REPOSITORY_NAME", "MODEL_NAME", "OBJECTTYPE", "OBJECT_NAM", "OBJECT_DESCRIPTION", "COLUMN_NAME"