Home » SQL & PL/SQL » SQL & PL/SQL » DDL generation (Oracle Database 19.10 Linux x86_64)
DDL generation [message #687894] |
Sat, 15 July 2023 05:33 |
smunir362
Messages: 310 Registered: September 2007
|
Senior Member |
|
|
I want to generate DDL of all constraints in the schemas but I do not want text like using and enable.
Please help me
|
|
|
Re: DDL generation [message #687896 is a reply to message #687894] |
Sat, 15 July 2023 10:00 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
smunir362 wrote on Sat, 15 July 2023 13:33I want to generate DDL of all constraints in the schemas but I do not want text like using and enable.
Please help me
Try
SET LINES 900 PAGES 20000
SET LONG 40000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
select dbms_metadata.get_ddl ( 'CONSTRAINT',CONSTRAINT_NAME,OWNER) FROM DBA_CONSTRAINTS WHERE OWNER='YOUR_OWNER';
To filter out the ENABLE/DISABLE you can use REPLACE, for example.
If you just want the names and the references, you can query DBA_CONSTRAINTS, columns OWNER,R_OWNER,TABLE_NAME,OWNER...
Regards,
Andrey
[Updated on: Sat, 15 July 2023 10:14] Report message to a moderator
|
|
|
Re: DDL generation [message #687897 is a reply to message #687894] |
Sat, 15 July 2023 11:19 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If you want to use DBMS_METADATA there is no ways to avoid USING and ENABLE/DISABLE.
The best you can have is:
SQL> begin
2 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
3 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
4 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);
5 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_CREATION', false);
6 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);
7 DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', false);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl
2 (decode(CONSTRAINT_TYPE, 'R','REF_CONSTRAINT', 'CONSTRAINT'), CONSTRAINT_NAME, OWNER)
3 FROM DBA_CONSTRAINTS
4 WHERE OWNER = 'SCOTT' and TABLE_NAME in ('EMP','DEPT')
5 /
DBMS_METADATA.GET_DDL(DECODE(CONSTRAINT_TYPE,'R','REF_CONSTRAINT','CONSTRAINT'),CONSTRAINT_NAME,OWNER)
----------------------------------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX ENABLE;
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX ENABLE;
3 rows selected.
But now if you just want the core part you can use, as Andrey said, the columns in DBA_CONSTRAINTS and DBA_CONS_COLUMNS, something like (simplified example, I assumed referencing and referenced tables in the same schema and restricted the query to 3 constraint types P, U and R, I let you do the same thing for the other ones):
SQL> set recsep each recsepchar ' '
SQL> with
2 cols as (
3 select table_name, constraint_name,
4 listagg('"'||column_name||'"',',') within group (order by position) cols
5 from dba_cons_columns
6 where owner = 'SCOTT' and table_name in ('EMP','DEPT')
7 group by table_name, constraint_name
8 )
9 select 'ALTER TABLE "'||c.owner||'"."'||c.table_name||'" ADD CONSTRAINT "'||c.constraint_name||'" '||
10 decode(c.constraint_type,
11 'P', 'PRIMARY KEY ('||cc.cols||')',
12 'U', 'UNIQUE ('||cc.cols||')',
13 'R', 'FOREIGN KEY ('||cc.cols||')
14 REFERENCES "'||c.owner||'"."'||cc2.table_name||'" ('||cc2.cols||')')||';'
15 res
16 from dba_constraints c, cols cc, cols cc2
17 where c.owner = 'SCOTT' and c.table_name in ('EMP','DEPT')
18 and cc.constraint_name = c.constraint_name
19 and cc2.constraint_name (+) = c.r_constraint_name
20 /
RES
------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO");
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO");
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO");
3 rows selected.
|
|
|
|
Re: DDL generation [message #687899 is a reply to message #687898] |
Mon, 17 July 2023 12:22 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can do it with some REPLACE:
SQL> select replace(
2 replace(
3 replace(
4 replace(
5 dbms_metadata.get_ddl
6 (decode(CONSTRAINT_TYPE, 'R','REF_CONSTRAINT', 'CONSTRAINT'), CONSTRAINT_NAME, OWNER),
7 'USING INDEX '),
8 'ENABLE'),
9 'DISABLE'),
10 chr(10)||' ') res
11 FROM DBA_CONSTRAINTS
12 WHERE OWNER = 'SCOTT' and TABLE_NAME in ('EMP','DEPT')
13 /
RES
------------------------------------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ;
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") ;
3 rows selected.
|
|
|
Re: DDL generation [message #687900 is a reply to message #687899] |
Mon, 17 July 2023 13:13 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel, this will not work if index is pre-created. For example:
drop table child purge
/
drop table parent purge
/
create table parent(
id number not null
)
/
create unique index parent_pk
on parent(
id
)
/
alter table parent
add constraint parent_pk
primary key(
id
)
/
create table child(
parent_id number
)
/
alter table child
add constraint child_fk1
foreign key(
parent_id
)
references parent
/
DBMS_METADATA will generate:
select dbms_metadata.get_ddl(decode(constraint_type,'R','REF_CONSTRAINT','CONSTRAINT'),constraint_name,owner) ddl
from dba_constraints
where owner = 'SCOTT'
and table_name in ('PARENT','CHILD')
/
DDL
-----------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."CHILD" ADD CONSTRAINT "CHILD_FK1" FOREIGN KEY ("PARENT_ID")
REFERENCES "SCOTT"."PARENT" ("ID") ENABLE;
ALTER TABLE "SCOTT"."PARENT" MODIFY ("ID" NOT NULL ENABLE);
ALTER TABLE "SCOTT"."PARENT" ADD CONSTRAINT "PARENT_PK" PRIMARY KEY ("ID")
USING INDEX "SCOTT"."PARENT_PK" ENABLE;
SQL>
Notice index name in PARENT_PK. Using your solution will fail to remove it:
select replace(
replace(
replace(
replace(
dbms_metadata.get_ddl
(decode(CONSTRAINT_TYPE, 'R','REF_CONSTRAINT', 'CONSTRAINT'), CONSTRAINT_NAME, OWNER),
'USING INDEX '),
'ENABLE'),
'DISABLE'),
chr(10)||' ') res
FROM DBA_CONSTRAINTS
WHERE OWNER = 'SCOTT' and TABLE_NAME in ('PARENT','CHILD')
/
RES
-------------------------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."CHILD" ADD CONSTRAINT "CHILD_FK1" FOREIGN KEY ("PARENT_ID")
REFERENCES "SCOTT"."PARENT" ("ID") ;
ALTER TABLE "SCOTT"."PARENT" MODIFY ("ID" NOT NULL );
ALTER TABLE "SCOTT"."PARENT" ADD CONSTRAINT "PARENT_PK" PRIMARY KEY ("ID")"SCOTT"."PARENT_PK" ;
SQL>
We do need regex. Something like:
select regexp_replace(
dbms_metadata.get_ddl(
decode(
constraint_type,
'R','REF_CONSTRAINT',
'CONSTRAINT'
),
constraint_name,
owner
),
' +((USING +INDEX .*)|ENABLE|DISABLE)([);])',
'\3'
) ddl
from dba_constraints
where owner = 'SCOTT'
and table_name in ('PARENT','CHILD')
/
DDL
-----------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."CHILD" ADD CONSTRAINT "CHILD_FK1" FOREIGN KEY ("PARENT_ID")
REFERENCES "SCOTT"."PARENT" ("ID");
ALTER TABLE "SCOTT"."PARENT" MODIFY ("ID" NOT NULL);
ALTER TABLE "SCOTT"."PARENT" ADD CONSTRAINT "PARENT_PK" PRIMARY KEY ("ID")
;
SQL>
SY.
P.S. Not sure why OP doesn't want to use your provided solution. It makes it much simpler.
[Updated on: Mon, 17 July 2023 13:16] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:41:56 CDT 2024
|