Informatica data base management Tables, Views,  Indexes Detail (Column Level) report

Lists the columns of database tables, views and indexes by schema.

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


select "L2_PRECISION",
"COLUMN_NAME",
"OBJECT_NAME",
"LX_LOCATION",
"IS_NULL_INDICATOR",
"COLUMN_DESCRIPTION",
"REPOSITORY_NAME",
"L2_DATATYPE",
"L2_SCALE",
"SCHEMA_NAME",
"OBJECT_TYPE",
"OBJECT_DESCRIPTION"
from (SELECT  DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME,  ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS SCHEMA_NAME,  ODS_IMA_ELEMENT.CLASS_TYPE AS OBJECT_TYPE,  ODS_IMA_ELEMENT.ELEMENT_NAME   AS OBJECT_NAME,  ( 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_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND  ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID)   AND  (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = 'Database Management' AND  ODS_IMA_PARENT_ELEMENT.CLASS_TYPE IN ('Schema',  'Database',  'User') AND  ODS_IMA_ELEMENT.CLASS_TYPE IN ('Index',  'Table',  'View',  'SnapshotTable') AND  ODS_IMA_CHILD_ELEMENT.CLASS_TYPE IN ('Column',  'IndexColumn') 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", "SCHEMA_NAME", "OBJECT_TYPE"