Generate Create Table Script for a Schema

Hi Experts:
In my schema, I have about 1500 tables. I would like to generate a "Create Table" script for all the tables in this schema. I tried using TOAD but it gets stalled.

Similarly, I would like a "Create View" script as well.

Lastly, A script that wold generate a script for ALL the Objects in the Schema.
Kamal AgnihotriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

joebednarzCommented:
I would use one of the Oracle packages...

DMBS_METADATA

So, you could do this:

SQL> spool ddl_script.sql
SQL> select dbms_metadata.get_ddl( 'TABLE', 'DEPT', 'SCOTT') FROM dual;
SQL> spool off;

If you want all the tables in a schema:

SQL> SELECT dbms_metadata.get_ddl( 'TABLE', table_name, user) FROM user_tables;
0
joebednarzCommented:
Same true for VIEWS...

SELECT dbms_metadata.get_ddl( 'VIEW', view_name, user ) FROM user_views;
0
joebednarzCommented:
I've not tried an "all objects" call to this package, but I imagine it might look like this:

SELECT dbms_metadata.get_ddl( object_type, object_name, user ) FROM user_objects;

... you might give it a try
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

YANN0SCommented:
0
Kamal AgnihotriAuthor Commented:
Hi Experts:
As suggested, I tried dbms_metadata.get_ddl    and I experineced strange behaviour.
here is the code.



0
Kamal AgnihotriAuthor Commented:
SELECT DBMS_METADATA.GET_DDL ('TABLE','ABS_SECT')||';' FROM DUAL;

The above Query returns the code below.

  CREATE TABLE "MOMENTUM"."ABS_SECT"
   (      "CD" VARCHAR2(10) NOT NULL ENABLE,

It shows only one column in the table ABS_SECT. However the table has 5 columns.

Please reply with a solution ASAP.
0
joebednarzCommented:
I'm assuming you using SQL*Plus... in that case, you probably should do some things before running the query:

SET HEADING OFF;
SET PAGESIZE 100;
SET LONG 10000;

Then,

SELECT dbms_metadata.get_ddl( ....

The problem is the output is a long data type and by default long data output is limited to (I think) the first 100 characters....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
>> I tried using TOAD but it gets stalled

Try for smaller batches like 500 or 250 objects at a time and see if they complete.
Then run them from multiple instances of TOAD if you think they are doing pretty good.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.