SQL-Server Import Script

Copy/paste into your favorite editor

--Import/Export Script for SQL-Server
--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"
, t.name AS "Table Technical Name"
, s.name AS "Schema"
, DB_NAME() AS "Database"
, '' AS "Business Definition"
, 'Database Table' AS "Physical Type"
, '' AS "Format"
--data element level
, '' AS "Data Element Business Name"
, c.name AS "Data Element Technical Name"
, '' AS "Business Definition"
, st.name AS "Data Type"
, c.max_length AS "Length/Precision"
, c.scale AS "Scale"
, CASE c.is_nullable WHEN 1 THEN 'Nullable' ELSE 'Not Nullable' END AS "Nullable"
 WHEN pk.object_id IS NOT NULL THEN 'Part of Primary Key'
 ELSE 'Not part of Primary Key'
 END AS "Primary Key"
from sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.columns c on t.object_id = c.object_id
left join sys.systypes st on c.user_type_id = st.xusertype
left join (
 select ix_c.object_id, ix_c.column_id
 from sys.indexes ix
 join sys.index_columns ix_c on ix.object_id = ix_c.object_id and ix.index_id = ix_c.index_id
 where ix.is_primary_key = 1
) pk on pk.object_id = c.object_id and pk.column_id = c.column_id

where 1=1
-- **use this to filter for certain schemas**
--and s.name in ('schema1','schema2')
-- **use this to filter for certain tables**
--and t.name in ('table1','table2')
-- **use this to filter for certain columns**
--and c.name in ('column1','column2')
order by s.name, t.name, c.name;

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