SQL: How to list / get / extract the DDL of one or all tables, indexes, views, and functions?

DDL stands for Data Definition Language. It is used to create and modify the structure of database objects such as tables, views and so forth.

Here are the general methods for getting the DDL for tables, indexes, views, and functions.

A. Table

1. For one table:

         SELECT DBMS_METADATA.GET_DDL('TABLE', '<table name>', '<schema>') FROM dual;

2. For all tables:

          SELECT DBMS_METADATA.GET_DDL ( 'TABLE', TABLE_NAME, '<schema>' ) FROM USER_TABLES;
 

B. Index

1. For one index

         SELECT DBMS_METADATA.GET_DDL('INDEX', '<index name>', '<schema>') FROM dual;

2. For all indexes:

          SELECT DBMS_METADATA.GET_DDL ( 'INDEX', INDEX_NAME, '<schema>' ) FROM USER_INDEXES WHERE INDEX_TYPE = 'NORMAL';

C. View

1. For one view:

          SELECT DBMS_METADATA.GET_DDL('VIEW', '<view name>', '<schema>') FROM dual;

2. For all views:

          SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, '<schema>') FROM USER_VIEWS;

D. Function

1. For one function:

           SELECT DBMS_METADATA.GET_DDL('FUNCTION', '<function name>', '<schema>') FROM dual;

2. For all functions:

          SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME, '<schema>') FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION';

E. DDL for all

          SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) FROM ALL_OBJECTS WHERE (OWNER = '<schema>') AND OBJECT_TYPE NOT IN('LOB', 'MATERIALIZED VIEW',  'TABLE PARTITION',  'PACKAGE BODY') ORDER BY OBJECT_TYPE, OBJECT_NAME;

-----------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.