MSSQL 테이블 스키마 조사 쿼리

************************************************************************************
1번 예제

SELECT
 a.COLUMN_NAME as [Name]
 ,CASE
  WHEN a.DATA_TYPE = 'numeric' THEN a.DATA_TYPE + '(' + CONVERT(varchar, a.NUMERIC_PRECISION) + ',' + CONVERT(varchar, NUMERIC_SCALE) + ')'
  ELSE
   CASE
    WHEN a.Character_Maximum_Length is NULL OR a.Character_Maximum_Length > 6000 THEN a.Data_Type
    ELSE a.Data_Type + '(' + ISNULL(CONVERT(varchar, a.Character_Maximum_Length), '') + ')'
  END
 END AS Type,
 CASE a.Is_Nullable
  WHEN 'YES' THEN 'AN' ELSE 'NN'
 END ETC,
 b.COLUMN_NAME AS PK
FROM
 INFORMATION_SCHEMA.COLUMNS a
 LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b
 ON a.TABLE_NAME = b.TABLE_NAME
 AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.TABLE_NAME = 'total_log'
ORDER BY a.ORDINAL_POSITION ASC

************************************************************************************


************************************************************************************
2번 예제

Select
  A.Column_Name as [NAME],
  A.Is_Nullable as [ISNULL],
  A.Data_Type as [TYPE],
  A.Character_Maximum_Length as [LENGTH],
  B.Column_Name as PK
from INFORMATION_SCHEMA.COLUMNS A
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE  B
 On A.Table_Name = B.Table_Name
 AND A.Column_Name = B.Column_Name
where A.Table_Name = '테이블명'

************************************************************************************