SELECT 'CREATE TABLE TB_CODE (' AS SCRIPT FROM DUAL
UNION ALL
SELECT ' ' || COLUMN_NAME || ' ' || DATA_TYPE || '(' ||DATA_LENGTH|| ')' || CASE WHEN NULLABLE = 'N' THEN ' NOT NULL ' ELSE ' ' END || ', '
FROM SYS.ALL_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'TB_CUST'
UNION ALL
SELECT ' CONSTRAINT ' || CONSTRAINT_NAME || ' ('
FROM ALL_CONSTRAINTS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'TB_CUST'
AND CONSTRAINT_TYPE = 'P'
AND STATUS = 'ENABLED'
AND VALIDATED = 'VALIDATED'
UNION ALL
SELECT D.COLUMN_NAME || '));'
FROM ALL_CONSTRAINTS M, ALL_CONS_COLUMNS D
WHERE M.OWNER = D.OWNER
AND M.TABLE_NAME = D.TABLE_NAME
AND M.CONSTRAINT_TYPE = 'P'
AND M.STATUS = 'ENABLED'
AND M.VALIDATED = 'VALIDATED'
AND M.OWNER = 'SCOTT'
AND M.TABLE_NAME = 'TB_CUST'
그냥 원쿼리로 프로시져나 함수처럼 스크립트를 가져오는 방법은 없습니다.
SELECT 'CREATE TABLE TB_CODE (' AS SCRIPT FROM DUAL
UNION ALL
SELECT ' ' || COLUMN_NAME || ' ' || DATA_TYPE || '(' ||DATA_LENGTH|| ')' || CASE WHEN NULLABLE = 'N' THEN ' NOT NULL ' ELSE ' ' END || ', '
FROM SYS.ALL_TAB_COLUMNS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'TB_CUST'
UNION ALL
SELECT ' CONSTRAINT ' || CONSTRAINT_NAME || ' ('
FROM ALL_CONSTRAINTS
WHERE OWNER = 'SCOTT'
AND TABLE_NAME = 'TB_CUST'
AND CONSTRAINT_TYPE = 'P'
AND STATUS = 'ENABLED'
AND VALIDATED = 'VALIDATED'
UNION ALL
SELECT D.COLUMN_NAME || '));'
FROM ALL_CONSTRAINTS M, ALL_CONS_COLUMNS D
WHERE M.OWNER = D.OWNER
AND M.TABLE_NAME = D.TABLE_NAME
AND M.CONSTRAINT_TYPE = 'P'
AND M.STATUS = 'ENABLED'
AND M.VALIDATED = 'VALIDATED'
AND M.OWNER = 'SCOTT'
AND M.TABLE_NAME = 'TB_CUST'
스토어드 프로시져로 만드시던가... 일일이 관련정보를 불러온 다음에..
그 정보를 바탕으로 스크립트를 생성해 주셔야 하는걸로 아는데요..
그럼..즐프...
위의 샘플은 초간단 ...대충..ㅡㅡ; 그냥 참고만 하세요..