select *
from
(

SELECT ALL_TAB_COMMENTS.TABLE_NAME,  
         ALL_TAB_COMMENTS.COMMENTS 
    FROM ALL_TAB_COMMENTS 
   WHERE ( ALL_TAB_COMMENTS.OWNER = 'SCOTT' )
ORDER BY  ALL_TAB_COMMENTS.COMMENTS  , ALL_TAB_COMMENTS.TABLE_NAME
) a,

(

SELECT A.TABLE_NAME,  
       A.COLUMN_NAME,  
       A.COMMENTS  ,
   B.COLUMN_ID ID,
   A.COMMENTS||'  '||DECODE(C.CONSTRAINT_NAME,NULL,'','PK') PNAME,
      DECODE(C.CONSTRAINT_NAME,NULL,'','PK') PK
FROM   ALL_COL_COMMENTS A ,
       ALL_TAB_COLUMNS B ,      
(SELECT t.CONSTRAINT_NAME ,  s.TABLE_NAME , s.COLUMN_NAME
FROM
(SELECT CONSTRAINT_NAME
     FROM   ALL_CONSTRAINTS
     WHERE    CONSTRAINT_TYPE = 'P' ) t ,
(SELECT  CONSTRAINT_NAME ,
         TABLE_NAME,COLUMN_NAME
FROM    ALL_CONS_COLUMNS  ) s
WHERE t.CONSTRAINT_NAME = s.CONSTRAINT_NAME ) C
WHERE  ( A.OWNER = B.OWNER ) AND
       ( A.TABLE_NAME = B.TABLE_NAME ) AND
       ( A.COLUMN_NAME = B.COLUMN_NAME ) AND
   A.TABLE_NAME = C.TABLE_NAME(+)  AND
       A.COLUMN_NAME = C.COLUMN_NAME(+)
ORDER BY A.TABLE_NAME,B.COLUMN_ID
) b

where  a.TABLE_NAME = b.TABLE_NAME

Posted by 나비 나비:D

BLOG main image
by 나비:D

공지사항

카테고리

분류 전체보기 (278)
Programming? (0)
----------------------------- (0)
나비의삽질 (5)
Application (177)
SQL (51)
Web (27)
etc. (14)
Omnia (0)
---------------------------.. (0)

글 보관함

달력

«   2018/01   »
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
Total : 903,520
Today : 217 Yesterday : 175