Memory Drive

반응형


DB의 테이블명이 DATA_20180801 형태로 되어있을경우 날짜가 포함된 테이블만 조회하는 정규식


SELECT * FROM ( 

        SELECT TABLE_NAME, REGEXP_SUBSTR( TABLE_NAME, '\d{4}\d{2}\d{2}') YYYYMMDD

        FROM USER_TABLES

)

WHERE YYYYMMDD IS NOT NULL

-- AND YYYYMMDD < '20180701'


결과 


반응형

반응형

-- 테이블 COMMENT 조회

SELECT TABLE_NAME, TABLE_TYPE, COMMENTS 

FROM USER_TAB_COMMENTS

WHERE COMMENTS IS NOT NULL;


-- 컬럼별 COMMENT 조회

SELECT * 

FROM USER_COL_COMMENTS

WHERE COMMENTS IS NOT NULL;


-- 테이블별 COMMENT 쿼리문

SELECT 'COMMENT ON TABLE ' || TABLE_NAME || ' IS ''' || COMMENTS || ''';' 

FROM USER_TAB_COMMENTS

WHERE COMMENTS IS NOT NULL;

COMMENT ON TABLE TABLE_NAME IS 'COMMENTS text';


-- 컬럼별 COMMENT 쿼리문

SELECT 'COMMENT ON COLUMN ' || TABLE_NAME || '.' || COLUMN_NAME || ' IS ''' ||  COMMENTS || ''';' 

FROM USER_COL_COMMENTS 

WHERE COMMENTS IS NOT NULL;

COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME IS 'COMMENTS';



반응형