테이블 칼럼 조회

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

, CASE WHEN  A.IS_NULLABLE = 'YES' THEN '' ELSE 'Not Null' END

, 'PK'= CASE WHEN OBJECTPROPERTY(OBJECT_ID(D.CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1 THEN 'Y' ELSE '' END

, B.VALUE AS COLUM_COMMENT

FROM INFORMATION_SCHEMA.COLUMNS A 

LEFT 

JOIN    SYS.EXTENDED_PROPERTIES B

ON B.major_id = object_id(A.TABLE_NAME) 

AND A.ORDINAL_POSITION = B.minor_id

LEFT 

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

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE D

ON A.TABLE_NAME = D.TABLE_NAME

AND A.COLUMN_NAME = D.COLUMN_NAME

ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION