CODEWITHSUNDEEP
sqldb2oracle-sqldeveloper
Prakul
Prakul

Reputation: 270

How to get CREATE TABLE Statement from existing table in db2?

I am using DB2 and Oracle SQL Developer. How to get the CREATE TABLE Statements from the existing tables?

There are too many tables and it will be a very lengthy process to do manually.

Upvotes: 0

Views: 2600

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

There is a special db2look utility for DDL extraction in Db2. You may refer to its options and their meaning at this link.

If you want SQL access to its capabilities, you may use the SYSPROC.DB2LK_GENERATE_DDL stored procedure supporting most of the utility's options. The routine has an output parameter getting "invocation number" int value after its call.
In case of a single table:

CALL SYSPROC.DB2LK_GENERATE_DDL ('-e -noview -t MY_SCHEMA.MY_TABLE', ?);

SELECT SQL_STMT
FROM SYSTOOLS.DB2LOOK_INFO_V
WHERE OP_TOKEN = <value_of_output_parameter_from_call_above>
ORDER BY OP_SEQUENCE;

Upvotes: 1

Simone Lungarella
Simone Lungarella

Reputation: 333

In SQLDeveloper if you can see the table there's the initial Create Table Statement in the SQL Tab

SQLDev - SQL Tab

You should do that for each table, this is a way to do it but I'm not sure it's fast enough for you.

Upvotes: 0

Related Questions