SPATIUM Desktop
주소복사
About Operating System Languages Tools Favorites Notice Visit TEST  
     Android (3)
     Linux (1)
     MacOS (1)
     OS (1)
     Solaris10 (15)
     Windows (1)
     Windows Server (2)
     Windows XP (3)
   ID  
   Password  
  |  
  Location United States
  IP Address 3.234.214.179
2019. 12
1234567
89101112
13
14
15161718192021
22232425262728
293031
Category  Languages, SQL
Writer 김태우 Date 2016-06-29 20:25:11 Visit 2167
테이블 정의서

 

 

SELECT A1.TABLE_NAME
           , A1.TABLE_COMMENTS
           , A1.COLUMN_ID
           , A1.COLUMN_NAME
           , A1.DATA_TYPE
           , A1.DATA_LENGTH         
           , (CASE
              WHEN B1.CONSTRAINT_TYPE = 'P'
              THEN 'Y'
              ELSE 'N'
              END) PK_FLAG
           , 'N' AS FK_FLAG
           , NVL(A1.NULL_FLAG, 'Y') AS NULL_FLAG 
           , A1.COLUMN_COMMENTS
  FROM (SELECT B.COMMENTS TABLE_COMMENTS
                        , A.TABLE_NAME TABLE_NAME
                        , C.COMMENTS COLUMN_COMMENTS
                        , A.COLUMN_NAME COLUMN_NAME
                        , (CASE A.NULLABLE
                           WHEN 'Y'
                           THEN 'N'
                           END) NULL_FLAG
                        , A.DATA_TYPE DATA_TYPE
                        , A.DATA_LENGTH 
                        , A.COLUMN_ID AS COLUMN_ID
                        , A.DATA_PRECISION
                FROM USER_TAB_COLUMNS A
                        , USER_TAB_COMMENTS B
                        , USER_COL_COMMENTS C
              WHERE (A.TABLE_NAME = B.TABLE_NAME)
                  AND (A.TABLE_NAME = C.TABLE_NAME
                          AND A.COLUMN_NAME = C.COLUMN_NAME
                          )
                  AND B.TABLE_TYPE = 'TABLE') A1
         , (SELECT A.TABLE_NAME
                       , A.COLUMN_NAME
                       , B.CONSTRAINT_TYPE
              FROM USER_CONS_COLUMNS A
                      , USER_CONSTRAINTS B
            WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
                AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE ( A1.TABLE_NAME = B1.TABLE_NAME(+)
     AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
  AND A1.TABLE_NAME LIKE '테이블명'
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID;
 
 
Tags  테이블 정의서
  Relation Articles
[Languages-SQL] 테이블 정의서 (2016-06-29 20:25:11)
  Your Opinion
Member ID
150 letters
Copyright (C) SPATIUM. All rights reserved.
[SPATIUM]WebMaster Mail