oracle删除外键的表的相关sql语句

1. 查找所有引用某表主键为外键的表

其中PK_SRVMONITOR_SERVICE为该表的主键名

Sql代码

select c.constraint_name,

c.table_name,

c.constraint_type,

c.r_constraint_name

from user_constraints c

where c.r_constraint_name = 'PK_SRVMONITOR_SERVICE'

and c.constraint_type = 'R'

2.删除SM_BIZSYS_RES表中的的FK_BIZSYS_RES_RES_ID外键

Sql代码

ALTER TABLE SM_BIZSYS_RES DROP CONSTRAINT FK_BIZSYS_RES_RES_ID

3.增加 CONSTRAINT FK_SM_LASTPERF表中的SRVMONITOR_SERVICE表中的主键作为外键

Sql代码

ALTER TABLE ADD CONSTRAINT FK_SM_LASTPERF_SRV_ID FOREIGN KEY(SRV_ID) REFERENCES SRVMONITOR_SERVICE(SRV_ID);

4.查找当前用户下所有的sequence

Java代码

select 'create sequence ' || sequence_name || ' minvalue ' || min_value ||

' maxvalue ' || max_value || ' start with ' || last_number ||

' increment by ' || increment_by || (case

when cache_size = 0 then

' nocache'

else

' cache ' || cache_size

end) || ';' seq

from user_sequences