**get_ddl_v1.sql
REM --This query is to display all DDL for a table .

REM --set output format

set pagesize 0
set feedback off
set long 90000
set echo off
SET VERIFY OFF
set heading off
set serveroutput on
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);

define file_name=ddl_output_script_
column  day new_value _day noprint
select to_char(sysdate,'yyyymmdd') day  from dual;
column table_name new_value table_name
column owner new_value owner
select table_name ,owner from dba_tables where table_name=upper('&table_name') and owner=upper('&owner');

spool &file_name.&owner._&table_name.&_day.sql

REM --get ddl script

select replace(dbms_metadata.get_ddl('TABLE','&table_name','&owner'),'"',null) FROM DUAL;
select replace(dbms_metadata.get_dependent_ddl('INDEX','&table_name','&owner'),'"',null) from dual;
--select replace(dbms_metadata.get_dependent_ddl('CONSTRAINT','&table_name','&owner'),'"',null) from dual;
select replace(dbms_metadata.get_dependent_ddl('TRIGGER','&table_name','&owner'),'"',null) from dual;
SELECT replace(DBMS_METADATA.GET_DEPENDENT_DDL  ('REF_CONSTRAINT','&table_name','&owner'),'"',null) from dual;

spool off


**Display constraint &foreign key constraint metadata

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','RLH_F1') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','DBINC_P') FROM DUAL;

SELECT DBMS_METADATA.get_ddl ('REF_CONSTRAINT', c.constraint_name)
  FROM user_constraints c  WHERE c.constraint_type = 'R' AND CONSTRAINT_NAME = 'RLH_F1';
   
SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', c.constraint_name)
  FROM user_constraints c  WHERE c.constraint_type = 'P' AND TABLE_NAME='&TABLE_NAME';

**Display constraint &foreign key column



SELECT CON.*,
       R.OWNER REF_OWNER,
       R.TABLE_NAME REF_TAB_NAME,
       R.COLUMN_NAME REF_COL_NAME
  FROM (SELECT C.OWNER,
               C.CONSTRAINT_NAME,
               C.CONSTRAINT_TYPE,
               C.TABLE_NAME,
               C.DELETE_RULE,
               C.R_CONSTRAINT_NAME,
               COL.COLUMN_NAME,
               COL.POSITION
          FROM USER_CONS_COLUMNS COL, USER_CONSTRAINTS C
         WHERE     C.OWNER = 'RCUSER'
               AND C.CONSTRAINT_NAME = 'RLH_F1'
               AND C.OWNER = COL.OWNER
               AND C.CONSTRAINT_NAME = COL.CONSTRAINT_NAME) CON,
       USER_CONS_COLUMNS R
 WHERE CON.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME;