1
Vote

Metadata

description

Implement process to grab metadata from source systems and store it locally. With every execution, client should send metadata update and new metadata should automatically be inserted / updated to metadata table. For SQL Server we can use script:

select
ISC.COLUMN_NAME,
isnull(OBJECTPROPERTY(OBJECT_ID(ISKCU.CONSTRAINT_SCHEMA + '.' + ISKCU.CONSTRAINT_NAME), 'IsPrimaryKey'), 0) as IsPrimaryKey,
ISC.ORDINAL_POSITION,
ISC.DATA_TYPE,
ISC.CHARACTER_MAXIMUM_LENGTH,
ISC.NUMERIC_PRECISION,
ISC.NUMERIC_SCALE,
case ISC.IS_NULLABLE when 'YES' then 1 when 'NO' then 0 else null end IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS ISC
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE ISKCU
on  ISC.TABLE_CATALOG = ISKCU.TABLE_CATALOG
and ISC.TABLE_SCHEMA = ISKCU.TABLE_SCHEMA
and ISC.TABLE_NAME = ISKCU.TABLE_NAME
and ISC.COLUMN_NAME = ISKCU.COLUMN_NAME
where ISC.TABLE_SCHEMA = 'dbo' and ISC.TABLE_NAME = 'FactInternetSales'

For Oracle / DB2 there is a query but it can't detect primary keys so this will have to be done manually (somehow)

comments