#title 확장속성
[[TableOfContents]]

==== ETL 문서화 ====
{{{
SELECT 
	'EXEC sys.sp_addextendedproperty ' + 
	CASE 
		WHEN B.Seq = 1 THEN 'N''1. Source IP'', '''', '
		WHEN B.Seq = 2 THEN 'N''2. Source DB'', '''', '
		WHEN B.Seq = 3 THEN 'N''3. Source Schema'', '''', '
		WHEN B.Seq = 4 THEN 'N''4. Source Table'', '''', '
		WHEN B.Seq = 5 THEN 'N''5. Source Column'', '''', '
		WHEN B.Seq = 6 THEN 'N''6. ETL Rule'', '''', '
	END + 
	'N''SCHEMA'', N''' + TABLE_SCHEMA + ''', ' + 
	'N''TABLE'', N''' + TABLE_NAME + ''', ' +
	'N''COLUMN'', N''' + COLUMN_NAME + ''''
FROM INFORMATION_SCHEMA.COLUMNS
	CROSS JOIN (
		SELECT 1 Seq UNION ALL 
		SELECT 2 Seq UNION ALL 
		SELECT 3 Seq UNION ALL 
		SELECT 4 Seq UNION ALL 
		SELECT 5 Seq UNION ALL 
		SELECT 6 Seq) B
}}}

{{{
select 
    case when a.minor_id = 1 then c.name + '.' + object_name(major_id) else '' end table_name
,   case when a.minor_id = 1 then (select top 1 value from sys.extended_properties where major_id = a.major_id and minor_id = 0 and name = 'MS_Description')  else '' end dim_name
,   a.value descriptions
,	d.name column_name
,	e.name data_type
,	d.max_length
,	d.precision
,	d.scale
,	isnull(f.column_desc3, '') index_info
,	isnull('(PK, ' + convert(varchar, i.type_desc) + ') ', '') + isnull('(FK,' + schema_name(b.schema_id) + '.' + object_name(g.referenced_object_id) + '.' + convert(nvarchar(200), h.referenced_key_name collate SQL_Latin1_General_CP1_CI_AS) + ')', '') key_info
from sys.extended_properties a
    inner join sys.objects b
        on a.major_id = b.object_id
    inner join sys.schemas c
        on b.schema_id = c.schema_id
	inner join sys.columns d
		on b.object_id = d.object_id
		and a.minor_id = d.column_id
	inner join sys.types e
		on d.system_type_id = e.system_type_id
	left join (
		select distinct
			a.object_id
		,	b.column_id
		,	stuff((
				select ',' + '(' + name + ',' +type_desc + ')' as [text()] --
				from (
					select 
						a.object_id
					,	a.name
					,	c.name col_name
					,	a.index_id
					,	b.column_id
					,	a.type_desc
					from sys.indexes a inner join sys.index_columns b
					on a.object_id = b.object_id
					and a.index_id = b.index_id inner join sys.all_columns c
					on b.object_id = c.object_id
					and b.column_id = c.column_id
					) t
				where t.object_id = b.object_id 
				and t.column_id = b.column_id
				for xml path('')), 1, 1, '') column_desc3
		from sys.indexes a inner join sys.index_columns b
		on a.object_id = b.object_id
		and a.index_id = b.index_id inner join sys.all_columns c
		on b.object_id = c.object_id
		and b.column_id = c.column_id
	) f
		on d.object_id = f.object_id
		and d.column_id = f.column_id
	left join sys.foreign_key_columns g
		on b.object_id = g.parent_object_id
		and d.column_id = g.parent_column_id
	left join (
		select
			b.*
		,	a.name referenced_key_name
		from sys.columns a
			inner join sys.foreign_key_columns b
				on a.object_id = b.referenced_object_id
				and a.column_id = b.referenced_column_id
	) h
		on b.object_id = h.parent_object_id
		and d.column_id = h.parent_column_id
	left join (
		select 
			a.object_id
		,	a.column_id
		,	b.type_desc
		,	b.name
		from sys.index_columns a inner join sys.indexes b
		on a.object_id = b.object_id 
		and a.index_id = b.index_id
		and b.is_primary_key = 1
	) i
		on d.object_id = i.object_id
		and d.column_id = i.column_id
where 1=1
--and major_id = object_id('dim.date')
and a.name = 'MS_Description'
and a.minor_id > 0
--and c.name <> 'code'
and e.name <> 'sysname'
order by c.name + '.' + object_name(major_id), a.minor_id
}}}