MS-SQL 2008 ] 테이블 컬럼 확장속성으로 설명 추가할 수 있는 도움 쿼리

 
DECLARE @TABLE_NAME NVARCHAR(776)
SET @TABLE_NAME = PARSENAME( '테이블 명' , 1 )

select COUNT( column_id ) from sys.columns
where OBJECT_ID = OBJECT_ID(@TABLE_NAME)

SELECT
a.name, b.value,
case when b.name is null then '' else
'EXEC sys.sp_dropextendedproperty '+
'@name=N''MS_Description'', '+
'@level0type=N''SCHEMA'', @level0name=N''dbo'', '+
'@level1type=N''TABLE'', @level1name=N'''+OBJECT_NAME(a.OBJECT_ID)+''', '+
'@level2type=N''COLUMN'', @level2name=N'''+a.NAME+'''' end as DropEx,

'EXEC sys.sp_addextendedproperty '+
'@name=N''MS_Description'', @value=N'''+'{0}'+''', '+
'@level0type=N''SCHEMA'', @level0name=N''dbo'', '+
'@level1type=N''TABLE'', @level1name=N'''+OBJECT_NAME(a.OBJECT_ID)+''', '+
'@level2type=N''COLUMN'',@level2name=N'''+a.NAME+'''' as AddEx
FROM sys.columns a
left outer join
(
 SELECT OBJECT_ID( @TABLE_NAME ) object_id , objtype, objname , name, value
 FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TABLE_NAME, 'column', default)
-- order by object_id, objname COLLATE  Korean_Wansung_CI_AS asc
) b
on a.object_id = b.object_id and a.name COLLATE  Korean_Wansung_CI_AS = b.objname COLLATE  Korean_Wansung_CI_AS
WHERE a.OBJECT_ID = OBJECT_ID(@TABLE_NAME)


-- 테이블 설명 추가...

EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'테이블 추가 설명 ex',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TABLE',  @level1name =  '테이블 명' ;

SELECT OBJECT_ID(  '테이블 명' ) object_id , objtype, objname , name, value
FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table',  '테이블 명', default, default)
where name = 'MS_Description'