#title 테이블 및 컬럼 설명 확인(확장프로시저)
[[TableOfContents]]

https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-extended-properties/

==== 간단한거 ====
{{{
use master
go

drop table #temp
create table #temp(
	dbname nvarchar(255)
,	object_nm nvarchar(255)
,	col_nm nvarchar(255)
,	value nvarchar(1000)
,	type nvarchar(20)
)

declare 
	@dbname nvarchar(255)
,	@sql nvarchar(4000)

declare tables cursor for
	select name from sys.databases

open tables
fetch next from tables into @dbname

while @@fetch_status = 0
begin

	set @sql = '
	use ' + @dbname + '
	select 
		db_name() dbname
	,	object_name(major_id) object_nm
	,	col_name(major_id, minor_id) col_nm
	,	convert(nvarchar(1000), value) value
	,	case when minor_id = 0 then ''table'' else ''column'' end type
	from sys.extended_properties'
	insert #temp exec(@sql)

	fetch next from tables into @dbname
end

close tables
deallocate tables

select * from #temp
--where col_nm is null --> 테이블 descriptions
}}}


==== 대충만든 복잡한거 ====
{{{
select
	a.name schema_name
,	isnull(
		case
			when c.column_id = 1 then b.name 
			else '' 
		end, '') table_name
,	isnull(
		case
			when c.column_id = 1 then d.table_name 
			else '' 
		end, '') table_desc
,	c.name column_name
,	isnull(d.column_name, '') column_desc
,	isnull('(PK, ' + convert(varchar, e.type_desc) + ') ', '') + isnull('(FK,' + schema_name(b.schema_id) + '.' + object_name(f.referenced_object_id) + ')', '') key_info
,	isnull(i.column_desc3, '') index_info
,	g.name data_type
,	case 
		when g.name IN ('nchar', 'nvarchar') AND c.max_length <> -1 then cast(c.max_length/2 as varchar) 
		when c.max_length = -1 then 'max'
		else cast(c.max_length as varchar) end column_length
,	case 
		when c.precision = 0 then '' 
		when c.max_length = -1 then '' 
		when g.name like '%decimal%' or
			g.name like '%real%' or
			g.name like '%float%' or
			g.name like '%numeric%' then '(' + cast(c.precision as varchar) + ', ' + cast(c.scale as varchar) + ')' 
 			else '' end ps
,	case when c.is_nullable = 1 then 'N' else 'NN' end is_nullable
,	case 
		when c.is_identity = 1 then 
			(select '(' + convert(varchar, seed_value)  + ',' + convert(varchar, increment_value) + ')' 
			from sys.identity_columns where b.object_id = object_id)
		else ''
	end is_identity
,	isnull(h.name, '') default_constraints
,	isnull(h.definition, '') default_value
from sys.schemas a inner join sys.objects b 
on a.schema_id = b.schema_id inner join sys.all_columns c
on b.object_id = c.object_id
and b.type = 'U' left join (
				select 
					a.major_id object_id
				,	b.minor_id column_id
				,	a.value table_name
				,	b.value column_name
				from sys.extended_properties a inner join sys.extended_properties b
				on a.major_id = b.major_id 
				and b.minor_id > 0 
				and a.minor_id = 0) d
on c.object_id = d.object_id
and c.column_id = d.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
					) e
on b.object_id = e.object_id
and c.column_id = e.column_id left join sys.foreign_key_columns f
on b.object_id = f.parent_object_id
and c.column_id = f.parent_column_id inner join sys.types g
on c.system_type_id = g.system_type_id 
and c.user_type_id = g.user_type_id left join sys.default_constraints h
on c.default_object_id = h.object_id 
and b.name <> 'sysdiagrams'
--and b.name = parsename('스키마.테이블명', 1) 
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) i
on c.object_id= i.object_id
and c.column_id = i.column_id
and b.object_id > 100
where 1=1
and b.name <> 'sysdiagrams'
order by 1, b.name , b.object_id, c.column_id
}}}