테이블 정의서 추출 쿼리

아래 생성한 컬럼을 테이블 정의서 만들때 사용한다.

해당 내역을 조회해 오는 쿼리

프로시저로 생성해서 사용해도 된다.



   SELECT  DISTINCT A.ORDINAL_POSITION  AS SEQ 
      , A.TABLE_CATALOG    AS DB 
      , A.TABLE_SCHEMA    AS USERID 
      , A.TABLE_NAME     AS T_NM 
      , ''       AS T_KOR_NM
      , A.COLUMN_NAME    AS C_NM 
      , (SELECT  [Description] = ex.value 
       FROM  sys.columns c 
       LEFT OUTER JOIN  sys.extended_properties ex ON  (ex.major_id = c.object_id
                       AND ex.minor_id = c.column_id 
                       AND ex.name = 'MS_Description')
       WHERE  OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
       AND OBJECT_NAME(c.object_id) = A.TABLE_NAME
       AND c.name = A.COLUMN_NAME) AS C_NM_KR  --> 2005이후 컬러명 조회 추후 테이블 깔끔하게 정리필요.
      , A.DATA_TYPE + CASE A.DATA_TYPE  
       WHEN 'varchar' THEN  ' ('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'    
       WHEN 'int' THEN  ' ('+LTRIM(STR(numeric_precision))+')'    
       WHEN 'numeric' THEN  ' ('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'   
       WHEN 'decimal' THEN  ' ('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'    
       WHEN 'datetime' THEN  '' 
       WHEN 'image'    THEN ''  
            WHEN 'char' THEN  ' ('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'  
       END                               AS TYPE 
      , '' AS '사용여부'
      , ISNULL((SELECT 'PK'  
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
       WHERE COLUMN_NAME = A.COLUMN_NAME  
      AND TABLE_NAME = A.TABLE_NAME
      ), '')   AS C_KEY 
      , CASE A.IS_NULLABLE 
       WHEN 'NO' THEN 'NN' 
       WHEN 'YES' THEN '' 
      END     AS NN 
      , ISNULL(A.COLUMN_DEFAULT, '')  AS DFT 
      , ', ' + '@' + A.COLUMN_NAME + ' ' + A.DATA_TYPE + CASE A.DATA_TYPE WHEN 'varchar' THEN  '('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'
                           WHEN 'int' THEN  '('+LTRIM(STR(numeric_precision))+')'    
                           WHEN 'numeric' THEN  '('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'   
                           WHEN 'decimal' THEN  '('+LTRIM(STR(numeric_precision))+'.'+LTRIM(STR(NUMERIC_SCALE))+')'    
                           WHEN 'datetime' THEN  '' 
                           WHEN 'image'    THEN ''  
                                WHEN 'char' THEN  '('+LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))+')'  
                           END AS ARG
      , ', ' + A.COLUMN_NAME   AS C_NM2
      , ', @' + A.COLUMN_NAME   AS C_NM3

    from information_schema.COLUMNS A , INFORMATION_SCHEMA.KEY_COLUMN_USAGE B 
    WHERE A.TABLE_NAME = '조회할테이블명'
    AND A.TABLE_NAME = B.TABLE_NAME 
    ORDER BY A.ORDINAL_POSITION