MS-SQL에서 Table정보를 Query로 조회할 때

아주 가끔이지만... 필요한 Query라서 메모성으로 기록한다.

MS-SQL에서 Query로 현재 생성된 Table의 정보를 조회할 때 사용하는 Query

SELECT A.NAME AS [TABLE_NAME]

, B.NAME AS [COLUMN_NAME]

, UPPER(C.NAME) AS [DATA_TYPE]

, (

CASE WHEN UPPER(C.NAME) LIKE '%CHAR'

THEN UPPER(C.NAME) + '(' + CONVERT(NVARCHAR(10), UPPER(CASE WHEN B.SYSTEM_TYPE_ID IN (231, 239)

THEN ( CASE WHEN B.MAX_LENGTH < 0 

THEN 'MAX'

ELSE CONVERT(NVARCHAR(10), B.MAX_LENGTH/2)

END

)

ELSE CONVERT(NVARCHAR(10), B.MAX_LENGTH)

  END))

+ ')'

ELSE UPPER(C.NAME)

END) AS [DATA_TYPE_LENGTH]

, (CASE WHEN B.IS_NULLABLE = 0 THEN 'N' ELSE 'Y' END) AS IS_NULLABLE

, (CASE WHEN B.IS_IDENTITY = 0 THEN ''  ELSE 'Y' END) AS IS_IDENTITY

, REPLACE(REPLACE(ISNULL(D.DEFINITION, ''), '(', ''), ')', '') [DEFAULT_VALUE]

  FROM SYS.TABLES A

INNER JOIN SYS.ALL_COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID

INNER JOIN SYS.TYPES C ON B.USER_TYPE_ID = C.USER_TYPE_ID

LEFT OUTER JOIN SYS.DEFAULT_CONSTRAINTS D ON B.OBJECT_ID = D.PARENT_OBJECT_ID AND B.COLUMN_ID = D.PARENT_COLUMN_ID

  ORDER BY A.NAME, B.COLUMN_ID

  ;