T-SQL::테이블 확장속성

테이블 확장 속성 확인 쿼리

-- 테이블에 대한 속성
SELECT object_name(major_id) as table_name, value
FROM sys.extended_properties 
WHERE  minor_id = 0
ORDER BY major_id

-- 컬럼 확장 속성
declare @tb_nm sysname
set @tb_nm = 'SF_COMMENT' 

SELECT object_name(major_id) as table_name, c.name ,value
FROM sys.extended_properties as p with(nolock)
	inner join sys.columns as c with(nolock) on p.major_id = c.object_id and p.minor_id = c.column_id
WHERE   p.class = 1 and p.minor_id != 0 and p.major_id = OBJECT_ID(@tb_nm)
order by p.major_id, p.minor_id


SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tb_nm, 'column', default)