0

A table in Oracle Database 11g contains the sequences and triggers if we are to use auto-increment fields.
I'm using the Oracle SQL Developer and couldn't find a solid way to create a consolidated table creation script.
Is it possible to create a consolidated DDL script with the below artefacts for a given table :

  1. Table creation
  2. Sequences
  3. Triggers
  4. Foreign keys
  5. Primary keys
Improve this question
1
  • Please edit your question to include a minimal reproducible example with: an example table; your expected output for that example; whether you want to create the table from scratch or if you want to reverse engineer the DDL from an existing implementation; and a definition of what you mean by "consolidated DDL script" (Do you mean a single DDL statement or do you mean several statements? In the latter case, why not use CREATE SEQUENCE ..., CREATE TABLE ... , ALTER TABLE ... ADD CONSTRAINT ... then CREATE TRIGGER ...?). Commented Nov 4, 2021 at 9:18

1 Answer 1

Reset to default
1

It is the DBMS_METADATA built-in package we use for such a purpose and its GET_DDL function.

More info in documentation.

Quite a few examples on Oracle-base site (scroll down, to the bottom of the page).

For a single table (which is what you asked), you'd run e.g.

select dbms_metadata.get_ddl('TABLE', 'EMP', 'SCOTT') from dual;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This doesn't include associated sequences and triggers. Any approach to do that?
What is "this"? DBMS_METADATA package contains everything. Did you read documentation about it? You might be intersted in GET_DEPENDENT_DDL function.
In 11g, sequences and tables have no relationship in the DB. It is purely user code that associates the two together. That said, usually you have a sequence named similarly to the primary key column, if that’s how you’ve done it for your application schema then you can easily code something to look up the likely sequence name and grab the DDL with dbms_metadata.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.