MySQL Import Script

Copy/paste into your favorite editor

<strong>--Script for exporting/importing Metadata from MySQL</strong>
select
<em><strong>--system level (fill in static data to have it returned by the query)</strong></em>
'' AS "System Name"
, '' AS "System Type"
, '' AS "Acronym"
, '' AS "Description"
, '' AS "Physical Type"
, '' AS "Service Level Description"
, '' AS "Version"
<em><strong>--table level</strong></em>
, '' AS "Table Business Name"
, c.table_name AS "Table Technical Name"
, c.table_schema AS "Schema"
, c.table_catalog AS "Database"
, '' AS "Business Definition"
, 'Database Table' AS "Physical Type"
, '' AS "Format"
<em><strong>--data element level</strong></em>
, '' AS "Data Element Business Name"
, c.column_name AS "Data Element Technical Name"
, '' AS "Business Definition"
, c.data_type AS "Data Type"
, CASE
WHEN numeric_precision_radix = 2 THEN coalesce(c.character_maximum_length, c.numeric_precision) -- convert numeric_precison from binary to decimal
ELSE coalesce(c.character_maximum_length, c.numeric_precision)
END AS "Length/Precision"
, CASE
WHEN numeric_precision_radix = 2 THEN c.numeric_scale -- convert numeric_precison from binary to decimal
ELSE c.numeric_scale
END AS "Scale"
, CASE c.is_nullable WHEN 'YES' THEN 'Nullable' ELSE 'Not Nullable' END AS "Nullable"
, CASE
WHEN kcu.column_name IS NOT NULL THEN 'Part of Primary Key'
ELSE 'Not part of Primary Key'
END AS "Primary Key"
from information_schema.columns c
left join information_schema.table_constraints tc on tc.table_catalog = c.table_catalog and tc.table_schema = c.table_schema and tc.table_name = c.table_name and tc.constraint_type = 'PRIMARY KEY'
left join information_schema.key_column_usage kcu on kcu.table_catalog = tc.table_catalog and kcu.table_schema = tc.table_schema and kcu.table_name = tc.table_name and kcu.constraint_name = tc.constraint_name and kcu.column_name = c.column_name
where 1=1
<em><strong>-- **use this to filter for certain schemas**</strong></em>
and c.table_schema in ('themapworks','schema2')
<em><strong>-- **use this to filter for certain tables**</strong></em>
--and c.table_name in ('table1','table2')
<em><strong>-- **use this to filter for certain columns**</strong></em>
--and c.column_name in ('column1','column2')
<strong>--Script updated 05-Jul-2017</strong>

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