테이블정의서 쿼리

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 = 'tbltest'
    AND A.TABLE_NAME = B.TABLE_NAME  
    ORDER BY A.ORDINAL_POSITION