[Oracle] 메타데이터 조회(continue..)

..

-- Oracle Version 확인
select * from v$version;

-- 접속계정(현재세션)의 정보 조회
SELECT  SYS_CONTEXT('USERENV', 'IP_ADDRESS') -- 접속한IP(The IP address of the client machine.)
FROM DUAL;

-- 테이블 컬럼 정보
  SELECT t.TABLE_NAME, tc.COMMENTS, t.COLUMN_ID, t.COLUMN_NAME
        ,t.DATA_TYPE
         ||NVL2(t.DATA_LENGTH, '('||TO_CHAR(t.DATA_LENGTH)
                                  ||NVL2(t.DATA_PRECISION, ','||TO_CHAR(t.DATA_PRECISION),'')
                              ||')', '') AS DATA_TYPE
        ,t.NULLABLE, cc.COMMENTS, t.DATA_DEFAULT AS DATA_DEFAULT
        ,A.POSITION AS PK_ORDER
    FROM USER_TAB_COLS t --테이블별 컬럼정보
        ,USER_TAB_COMMENTS tc -- 테이블 코멘트
        ,USER_COL_COMMENTS cc  -- 컬럼 코멘트
        ,(SELECT ct.TABLE_NAME, ct_col.COLUMN_NAME, ct_col.POSITION
            FROM ALL_CONSTRAINTS  ct --제약조건
                ,ALL_CONS_COLUMNS ct_col --제약조건 컬럼
           WHERE ct.CONSTRAINT_TYPE = 'P' 
             AND ct.OWNER = ct_col.OWNER
             AND ct.CONSTRAINT_NAME = ct_col.CONSTRAINT_NAME
         ) A
   WHERE 1=1
     AND tc.TABLE_TYPE = 'TABLE'
     AND t.TABLE_NAME = tc.TABLE_NAME(+)
     AND t.TABLE_NAME = cc.TABLE_NAME(+) AND t.COLUMN_NAME = cc.COLUMN_NAME(+)
     AND t.TABLE_NAME = tc.TABLE_NAME(+)
	 AND t.TABLE_NAME = A.TABLE_NAME(+)
     AND t.COLUMN_NAME = A.COLUMN_NAME(+)
     AND t.TABLE_NAME IN ('대상테이블명')
ORDER BY t.TABLE_NAME, t.COLUMN_ID;


-- 시퀀스 조회
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE '%시퀀스명%';

--제약조건 조회 :CONSTRAINT_TYPE=P(PK)/C(제약조건)
SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE
     , B.COLUMN_NAME , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = '테이블명'
   AND A.CONSTRAINT_TYPE = 'P' --P(PrimaryKey)|C(제약조건)|..
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION;
 
--프로시저 내용 검색
SELECT T1.OBJECT_ID, T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TEXT
  FROM USER_OBJECTS T1
 INNER JOIN USER_SOURCE T2
    ON T1.OBJECT_NAME = T2.NAME
 WHERE T1.OBJECT_TYPE IN ('PROCEDURE')--, 'FUNCTION')
   AND T2.TEXT LIKE '%EXECUTE%'  /* 검색어 */
  ;

--CREATE TABLE 컬럼정의 구문 뽑기
SELECT COLUMN_NAME||' '
    ||DATA_TYPE||CASE DATA_TYPE WHEN 'NUMBER' THEN DECODE(DATA_LENGTH, 22, '', '('||DATA_LENGTH||')')
                     WHEN 'VARCHAR2' THEN '('||DATA_LENGTH||')' END
    ||DECODE(NULLABLE, 'Y', ' NOT NULL ', '')||', '  
    FROM USER_TAB_COLS WHERE TABLE_NAME = '테이블명';
/* REF_VALUE_18 VARCHAR2(100) NOT NULL , 
   REF_VALUE_19 VARCHAR2(100) NOT NULL , 
   REF_VALUE_20 VARCHAR2(100) NOT NULL ,   */
   
--INSERT문 컬럼목록 만들기
SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) 
  FROM USER_TAB_COLS WHERE TABLE_NAME = '테이블명';