MSSQL 에서 sp_help "테이블명" 으로 하면 테이블의 정보를 확인할 수 있으나
컬럼의 Comment까지 같이 확인은 안된다.
아래는 테이블의 컬럼Comment를 확인할 수 있는 쿼리이다.
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 = '테이블명'
ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION
SELECT A.COLUMN_ID,
A.COLUMN_NAME,
A.DATA_TYPE,
CASE WHEN A.DATA_TYPE = 'NUMBER' THEN A.DATA_PRECISION ||','|| A.DATA_SCALE
WHEN A.DATA_TYPE = 'DATE' THEN '7'
ELSE TO_CHAR(DATA_LENGTH)
END DATA_SIZE,
B.COMMENTS COMMENTS,
DECODE(D.CONSTRAINT_TYPE, 'P', 'PK') AS KEY_FIELD
FROM ALL_TAB_COLUMNS A INNER JOIN ALL_COL_COMMENTS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.OWNER = B.OWNER
AND A.COLUMN_NAME= B.COLUMN_NAME
LEFT OUTER JOIN ALL_CONS_COLUMNS C
ON A.TABLE_NAME = C.TABLE_NAME
AND A.OWNER = C.OWNER
AND A.COLUMN_NAME = C.COLUMN_NAME
AND C.POSITION IS NOT NULL
LEFT OUTER JOIN ALL_CONSTRAINTS D
ON C.TABLE_NAME = D.TABLE_NAME
AND C.OWNER = D.OWNER
AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
AND D.CONSTRAINT_TYPE = 'P'
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.COLUMN_ID
출처 : http://blog.naver.com/PostView.nhn?blogId=2zerox&logNo=90150539116
[출처] MSSQL 테이블 정보 쿼리(Comment 포함)|작성자 수정이아빠