Search code examples
sqldatabaseoracle-databaseplsqlprocedure

Get DDL command from oracle database objects without using dbms_metadata


I am doing a procedure in the oracle database that has the function of performing a kind of inventory of the objects of the database.

Basically I must get the DDL of objects of type table.

For this, I am using queries from the bank itself as:

select * from user_objects; 
select * from user_constraints;
select * from user_source;

My inventory must contain the following information:

Inventory information here.

How do I get the DDL command from objects without using the function:

dbms_metadata.get_ddl(); 

and no other ready functions from the metadata library.

I have also tried this:

SELECT 
(CASE WHEN line = 1 THEN 'create or replace ' || text ELSE text END) texto 
FROM user_source
WHERE NAME = '....'
ORDER BY line

but this command does not get the ddl of table objects.


Solution

  • For getting the DDL of views, it's very easy:

    SELECT VIEW_NAME, TEXT FROM ALL_VIEWS; 
    

    If you want it to return just the text of a particular view, then do:

    SELECT TEXT FROM ALL_VIEWS
    WHERE VIEW_NAME LIKE '[name_of_view]';
    

    Getting the DDL for tables is more cumbersome, but can be done by querying the data from several system views:

    ALL_TABLES
    ALL_TAB_COLUMNS
    ALL_COL_COMMENTS
    ALL_CONSTRAINTS
    ALL_CONS_COLUMNS
    ALL_INDEXES
    ALL_IND_COMMENTS

    For example, if you wanted to get all column names and their data types for TABLE1, you would do:

    SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME LIKE 'TABLE1';
    

    To get a list of all constraints on a table, the query is:

    SELECT * FROM ALL_CONSTRAINTS
    WHERE TABLE_NAME LIKE 'TABLE1';
    

    To get a full table definition takes a fairly good understanding of how to use these system views. A very helpful page for this can be found here: 6 Useful Oracle Data Dictionary Queries Every DBA Should Have