본문 바로가기

database/oracle

테이블 명세서(정의서) 생성 쿼리

 SELECT COLUMN_ID AS SEQ,
         (SELECT NVL (D.POSITION, '')
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'P')
            AS PK,
         (SELECT CASE WHEN D.POSITION IS NULL THEN '' ELSE 'V' END
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'R')
            AS FK,
         A.COLUMN_NAME AS COLUMN_ID,
         C.COMMENTS,
         A.DATA_TYPE,
         A.DATA_LENGTH,
         A.NULLABLE,
         A.DATA_DEFAULT
    FROM ALL_TAB_COLUMNS A, ALL_TAB_COMMENTS B, ALL_COL_COMMENTS C
   WHERE     A.OWNER = UPPER ( :user_name)
         AND B.OWNER = UPPER ( :user_name)
         AND C.OWNER = UPPER ( :user_name)
         AND A.OWNER = B.OWNER
         AND A.OWNER = C.OWNER
         AND A.TABLE_NAME = B.TABLE_NAME
         AND A.TABLE_NAME = C.TABLE_NAME
         AND A.COLUMN_NAME = C.COLUMN_NAME
         AND A.TABLE_NAME = UPPER ( :table_name)
ORDER BY 1
MSSQL은 어떻게 할까나...