Oracle12c 以上导出当前用户下所有外键、主键、索引、触发器 的DDL

原创文章4年前 (2020)发布 fdadmin
3,125 0 0

–导出索引 

SELECT 'CREATE ' || DECODE(B.INDEX_TYPE, 'NORMAL', '', B.INDEX_TYPE) ||
DECODE(B.UNIQUENESS, 'NONUNIQUE', '', B.UNIQUENESS) || ' INDEX ' ||
B.INDEX_NAME || ' ON ' || B.TABLE_NAME || ' (' ||
(SELECT TO_CHAR(listagg(A.COLUMN_NAME) WITHIN GROUP(ORDER BY COLUMN_NAME))
FROM USER_IND_COLUMNS A
WHERE A.INDEX_NAME = B.INDEX_NAME) || ');'
FROM USER_INDEXES B

–导出主键

SELECT 'ALTER TABLE ' || B.TABLE_NAME || ' ADD CONSTRAINT ' ||
B.CONSTRAINT_NAME || ' PRIMARY KEY (' ||
(SELECT TO_CHAR(listagg(A.COLUMN_NAME,',') within group (order by COLUMN_NAME))
FROM USER_CONS_COLUMNS A
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) || ');'
FROM USER_CONSTRAINTS B
WHERE B.CONSTRAINT_TYPE = 'P'

–导出外键

SELECT 'ALTER TABLE ' || B.TABLE_NAME || ' ADD CONSTRAINT ' ||

B.CONSTRAINT_NAME || ' FOREIGN KEY (' ||
(SELECT TO_CHAR(listagg(A.COLUMN_NAME) WITHIN GROUP(ORDER BY COLUMN_NAME))
FROM USER_CONS_COLUMNS A
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) || ') REFERENCES ' ||
(SELECT B1.table_name FROM USER_CONSTRAINTS B1
WHERE B1.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || '(' ||
(SELECT TO_CHAR(listagg(A.COLUMN_NAME) WITHIN GROUP(ORDER BY COLUMN_NAME))
FROM USER_CONS_COLUMNS A
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || ');'
FROM USER_CONSTRAINTS B
WHERE B.CONSTRAINT_TYPE = 'R'

–导出触发器

–方法1:
plsql develop调用exp:tools-》export object—》trigger

–方法2:

select dbms_metadata.get_ddl(object_type=>'TRIGGER',NAME=>TRIGGER_NAME) FROM USER_TRIGGERS;

© 版权声明

相关文章

暂无评论

暂无评论...