Database Management Available reports.
This page will show you all the Available reports for Database Management, once you select a report name, it will open the report query page. copy and paste it to your PC and run it from, toad or any other DB tools you have.
Available reports
Lists Database repository 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.
select “TABLE_NAME”,
“CONSTRAINT_TYPE”,
“COLUMN_NAME”,
“LX_LOCATION”,
“CONSTRAINT_NAME”,
“REPOSITORY_NAME”,
“SCHEMA_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME as REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME as SCHEMA_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_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, 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_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ASOC.FROM_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 = ‘Database Management’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Table’, ‘SnapshotTable’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘Column’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE IN (‘Schema’, ‘User’, ‘Database’) AND ODS_CHILD_LVL2_ELMT.CLASS_TYPE IN (‘CheckConstraint’, ‘ForeignKey’, ‘PrimaryKeyConstraint’, ‘UniqueConstraint’, ‘NotNullConstraint’) AND ODS_CHILD_LVL2_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE ‘%N%’ )) “query”
order by “REPOSITORY_NAME”, “SCHEMA_NAME”
Lists database synonyms and the tables using those synonyms.
select “BASE_OBJECT_TYPE”,
“REPOSITORY_NAME”,
“OBJECT_DESCRIPTION”,
“SYNONYM_DESCRIPTION”,
“BASE_OBJECT_NAME”,
“SYNONYM_NAME”,
“SCHEMA_NAME1”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME AS REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS SCHEMA_NAME1 , ODS_IMA_ELEMENT.CLASS_TYPE AS Base_OBJECT_TYPE, ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS SYNONYM_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 SYNONYM_DESCRIPTION, ODS_IMA_ELEMENT.ELEMENT_NAME AS BASE_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 FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (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_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_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_CHILD_ELEMENT.CLASS_TYPE = ‘Synonym’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Table’, ‘View’) 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 = ‘N’ )) “query”
order by “REPOSITORY_NAME”, “SCHEMA_NAME1”, “BASE_OBJECT_TYPE”, “SYNONYM_NAME”, “SYNONYM_DESCRIPTION”
Lists the columns of database tables, views and indexes by schema.
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”
Lists database tables, views and indexes by schema.
select “REPOSITORY_NAME”,
“LEVEL1_ELEMENT”,
“OBJECT_NAME”,
“OBJECT_DESCRIPTION”,
“SCHEMA_NAME”,
“LX_LOCATION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS SCHEMA_NAME, ODS_IMA_ELEMENT.CLASS_TYPE AS LEVEL1_ELEMENT, 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_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_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_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “SCHEMA_NAME”, “LEVEL1_ELEMENT”