Oracle Import Script

Copy/paste into your favorite editor

–Script for exporting/importing metatdata from Oracle

select
–system level (fill in static data to have it returned by the query)
‘ ‘ AS “System Name”
, ‘ ‘ AS “System Type”
, ‘ ‘ AS “Acronym”
, ‘ ‘ AS “Description”
, ‘ ‘ AS “Physical Type”
, ‘ ‘ AS “Service Level Description”
, ‘ ‘ AS “Version”
–table level
, ‘ ‘ AS “Table Business Name”
, c.TABLE_NAME AS “Table Technical Name”
, ‘ ‘ AS “Schema”
, ‘ ‘ AS “Database”
, ‘ ‘ AS “Business Definition”
, ‘Database Table’ AS “Physical Type”
, ‘ ‘ AS “Format”
–data element level
, ‘ ‘ AS “Data Element Business Name”
, c.COLUMN_NAME AS “Data Element Technical Name”
, ‘ ‘ AS “Business Definition”
, c.DATA_TYPE AS “Data Type”
, c.DATA_LENGTH AS “Length/Precision”
, c.DATA_SCALE AS “Scale”
, CASE c.NULLABLE WHEN ‘Y’ THEN ‘Nullable’ ELSE ‘Not Nullable’ END AS “Nullable”
, CASE
WHEN pk.pk_count IS NOT NULL THEN ‘Part of Primary Key’
ELSE ‘Not part of Primary Key’
END AS “Primary Key”
from ALL_TAB_COLUMNS c
left join (
select cons_col.OWNER, cons_col.TABLE_NAME, cons_col.COLUMN_NAME, COUNT(*) as pk_count
from ALL_CONSTRAINTS cons
join ALL_CONS_COLUMNS cons_col on cons.OWNER = cons_col.OWNER and cons.TABLE_NAME = cons_col.TABLE_NAME and cons.CONSTRAINT_NAME = cons_col.CONSTRAINT_NAME
where cons.CONSTRAINT_TYPE = ‘P’
group by cons_col.OWNER, cons_col.TABLE_NAME, cons_col.COLUMN_NAME
) pk on c.OWNER = pk.OWNER and c.TABLE_NAME = pk.TABLE_NAME and c.COLUMN_NAME = pk.COLUMN_NAME
where 1=1
— **use this to filter for certain schemas/owners**
and c.OWNER in (‘MAPWORKS_USER’,’owner2′)
— **use this to filter for certain tables**
–and c.TABLE_NAME in (‘COUNTRIES’,’table2′)
— **use this to filter for certain columns**
–and c.COLUMN_NAME in (‘column1′,’column2’)
order by c.TABLE_NAME, c.COLUMN_NAME
;

–Script updated 05-Jul-2017

Looking for scripts for other DBMS platforms?

If you run into any problems, please drop us a Support Request or email us anytime at answers@themapworks.com