2014년 6월 9일 월요일

MSSQL 테이블 커럼 커멘트 조회

SELECT
 A.TABLE_NAME, C.VALUE AS TABLE_COMMENT, A.COLUMN_NAME, A.DATA_TYPE, ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),  
 CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH,
 A.COLUMN_DEFAULT, A.IS_NULLABLE, B.VALUE AS COLUM_COMMENT
FROM
 INFORMATION_SCHEMA.COLUMNS    A
 LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B ON B.major_id = object_id(A.TABLE_NAME)  AND A.ORDINAL_POSITION = B.minor_id
 LEFT OUTER JOIN
 (
  SELECT object_id(objname) AS TABLE_ID, VALUE FROM ::FN_LISTEXTENDEDPROPERTY
  (NULL, 'User','dbo','table', NULL, NULL, NULL)
 )          C ON object_id(A.TABLE_NAME) = C.TABLE_ID
WHERE
 A.TABLE_NAME = 'labchemicalinfo'
ORDER BY
 A.TABLE_NAME, A.ORDINAL_POSITION

댓글 없음:

댓글 쓰기