컬럼 정보 보기 쿼리

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,

CASE WHEN ISNULL(D.NAME,'') = '' THEN '' ELSE 'auto_increment' END EXTRA

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

syscolumns D 

ON D.ID = object_id(A.TABLE_NAME) AND A.COLUMN_NAME = D.NAME AND D.COLSTAT & 1 = 1 

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 = 'board'

ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION