[MSSQL] 테이블 명세서 출력

--. 테이블 명세서 출력

SELECT A.TABLE_SCHEMA AS [스키마]

, A.TABLE_NAME AS [테이블명]

, A.COLUMN_NAME AS [컬럼명]

, B.ColumnDescription AS [컬럼 설명]

, A.ORDINAL_POSITION AS [순서]

, A.DATA_TYPE AS [데이터 타입]

,   CASE 

WHEN A.CHARACTER_MAXIMUM_LENGTH IS NULL THEN 

ISNULL(A.NUMERIC_PRECISION, '')

ELSE A.CHARACTER_MAXIMUM_LENGTH 

END AS [길이]

, A.IS_NULLABLE AS [NULL 여부]

, ISNULL(A.COLUMN_DEFAULT, '')     AS [디폴트 값]

FROM  INFORMATION_SCHEMA.COLUMNS AS A WITH (NOLOCK)

INNER JOIN (

SELECT A.name AS TableName

, B.name AS ColumnName

, C.Value     AS ColumnDescription

FROM sys.tables AS A WITH (NOLOCK)

INNER JOIN sys.columns AS B WITH (NOLOCK)

ON A.object_id = B.object_id

INNER JOIN sys.extended_properties AS C WITH (NOLOCK)

ON A.object_id = C.major_id

AND B.column_id = c.minor_id

) AS B

ON A.TABLE_NAME = B.TableName

AND A.COLUMN_NAME = B.ColumnName

WHERE A.TABLE_NAME = 'tb_Test'