SPATIUM Desktop
주소복사
About Operating System Languages Tools Favorites Notice Visit TEST  
     ADK (2)
     android (3)
     ASP (5)
     ASP.Net (1)
     C# (5)
     HTML (9)
     JAVA (6)
     JavaScript (9)
     jQuery (5)
     Mobile Web (2)
     Oracle (8)
     PHP (7)
     Spring Framework (4)
     SQL (15)
     WPF (5)
   ID  
   Password  
  |  
  Location United States
  IP Address 54.224.133.152
2018. 06
12
3456789
10111213141516
17181920212223
24
25
2627282930
Category  Languages, SQL
Writer 김태우 Date 2016-06-29 20:25:11 Visit 1587
테이블 정의서

 

 

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