Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL

But this statement also generates all primary and foreign key scripts that belong to table. So, is there any way to not include primary and foreign keys in create table scripts

share|improve this question
    
Why don't you want at least primary keys?! –  Colin 't Hart Apr 8 at 7:24
    
I don't want primary keys. I want table script without primary keys because I have too many tables and it causes problems when inserting these tables to another location. –  osman Apr 8 at 7:32

1 Answer 1

up vote 2 down vote accepted

You could try the following:

set pagesize 0
set long 90000 

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);

SELECT DBMS_METADATA.GET_DDL( 'TABLE','table_name','schema') FROM DUAL;

This should result in the DDL without any indexes and foreign keys.

Reference:

share|improve this answer
    
When I paste and copy above to TOAD it gave ORA-00900. But I changed your statement by adding "execute" statement to each proc, then it worked. Thanks –  osman Apr 8 at 8:52
    
But I really don't understand why it did not work as your statement. Do you have any idea? –  osman Apr 8 at 8:53
    
@osman they made an error, it doesn't work in query mode in sqldeveloper or in sqlplus. i've fixed the error, waiting on edit to be approved. –  booyaa Apr 8 at 9:18
    
@booyaa I run this "SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE' and object_name = 'SET_TRANSFORM_PARAM'" but got zero. I think I could find one? –  osman Apr 8 at 9:30
    
sorry for the typo. Was on the run to a meeting. Yes, @booyaa is right. EXEC for each line is correct. –  hot2use Apr 8 at 11:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.