Oracle脚本分享:get_table_ddl.sql

Oracle脚本分享:get_table_ddl.sql

使用SQL Developer等工具可以方便/快速地生成一个表的SQL语句(DDL语句)，其实我们也可以用SQL生成一个表的DDL相关语句。这里分享一个这样的SQL，如下所示：

/*-***********************************************************************************************
--ScriptName          :           get_table_ddl.sql
--Author              :           潇湘隐者  
--CreateDate          :           2018-06-15
--Description         :           输出表、表的注释、表的索引、约束、对象权限的DDL语句.
**************************************************************************************************
--Parameters          :                                   参数说明
**************************************************************************************************
    TABLE_OWNER        表的OWNER
    TABLE_NAME         表名
**************************************************************************************************
注意事项：
         DBMS_METADATA.SET_TRANSFORM_PARAM中的STORAGE，SEGMENT_ATTRIBUTES，TABLESPACE等属性设置会
         影响脚本的输出，根据实际需求调整这些参数。
**************************************************************************************************
   Modified Date    Modified User     Version                Modified Reason
**************************************************************************************************
    2018-06-15      潇湘隐者             V1.0      新建此脚本
    2022-05-02      潇湘隐者             V1.1      增加TRIM函数，防止用户输入时，带入空格导致
                                                  查询结果不正确;另外，添加部分英文注释
**************************************************************************************************/
set echo off
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON SERVEROUTPUT ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',true);
--用于生成间隔分区表的，默认间隔分区表不生成自动创建的分区
EXECUTE dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);

SELECT 'the current host_name is ' || host_name || '. the instance_name is ' || instance_name 
FROM V$INSTANCE;

SHOW USER;


ACCEPT TABLE_OWNER CHAR PROMPT 'Enter Table Owner : '
ACCEPT TABLE_NAME CHAR PROMPT 'Enter Table Name : '

--table definition
SELECT '--table''s definition ...' FROM DUAL;

SELECT DBMS_METADATA.GET_DDL ('TABLE', OBJECT_NAME, OWNER)  
FROM DBA_OBJECTS
WHERE   owner = UPPER (TRIM('&TABLE_OWNER'))
    AND object_name = UPPER (TRIM('&TABLE_NAME'))
    AND object_type = 'TABLE';

--table comment and column comment
SELECT '--table''s comment and column''s comment...' FROM DUAL;

SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER)  
FROM (SELECT table_name, owner
        FROM DBA_COL_COMMENTS
      WHERE     owner = UPPER (TRIM('&TABLE_OWNER'))
               AND table_name = UPPER (TRIM('&TABLE_NAME'))
               AND comments IS NOT NULL
      UNION
      SELECT table_name, owner
        FROM DBA_TAB_COMMENTS
      WHERE     owner = UPPER (TRIM('&TABLE_OWNER'))
             AND table_name = UPPER (TRIM('&TABLE_NAME'))
             AND comments IS NOT NULL);

--index definition
SELECT '--table''s index definition...' FROM DUAL;

SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('INDEX', TABLE_NAME, TABLE_OWNER)  
FROM (SELECT table_name, table_owner
      FROM Dba_indexes
         WHERE     table_owner = UPPER (TRIM('&TABLE_OWNER'))
               AND table_name = UPPER (TRIM('&TABLE_NAME'))
               AND index_name NOT IN
                      (SELECT constraint_name
                         FROM DBA_CONSTRAINTS
                        WHERE     table_name = table_name
                              AND constraint_type = 'P')
               AND ROWNUM = 1);
              
--trigger definition
SELECT '--table''s trigger definition....' FROM DUAL;

SELECT DBMS_METADATA.GET_DDL ('TRIGGER', trigger_name, owner) 
FROM Dba_triggers
WHERE   table_owner = UPPER (TRIM('&TABLE_OWNER'))
    AND table_name = UPPER (TRIM('&TABLE_NAME'));

--referenced contraint definition
SELECT '--table''s referenced contraint definition....' FROM DUAL;

SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('REF_CONSTRAINT', table_name, OWNER)  
FROM DBA_CONSTRAINTS
WHERE   owner = UPPER (TRIM('&TABLE_OWNER'))
    AND table_name = UPPER (TRIM('&TABLE_NAME'))
    AND CONSTRAINT_TYPE = 'R'
    AND ROWNUM = 1;
    

--table constraint definition
SELECT '--table''s constraint definition....' FROM DUAL;

SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('CONSTRAINT', TABLE_NAME, OWNER)  
FROM DBA_CONSTRAINTS
WHERE   owner = UPPER (TRIM('&TABLE_OWNER'))
    AND table_name = UPPER (TRIM('&TABLE_NAME'))
    AND CONSTRAINT_TYPE <> 'R'
    AND ROWNUM = 1;
    
--table's grant definition
SELECT '--table''s grant right definition....' FROM DUAL;

SELECT DBMS_METADATA.get_dependent_ddl ('OBJECT_GRANT', TABLE_NAME, OWNER) 
FROM DBA_TAB_PRIVS
WHERE   owner = UPPER (TRIM('&TABLE_OWNER'))
    AND table_name = UPPER (TRIM('&TABLE_NAME'))
    AND ROWNUM = 1;

--table's synonym definition
SELECT '--table''s synonym definition....' FROM DUAL;

SELECT DBMS_METADATA.GET_DDL ('SYNONYM', synonym_name, owner)  
FROM dba_synonyms 
WHERE   table_owner = UPPER (TRIM('&TABLE_OWNER'))
    AND table_name  = UPPER (TRIM('&TABLE_NAME'));
/
undefine TABLE_OWNER;
undefine TABLE_NAME;

在SQL*Plus中调用get_table_ddl.sql，执行后，输入&table_owner,&table_name两个参数，就可以得到一个表的DDL相关语句。