oracle非空约束查询 IS NOT NULL
1、oracle查询表的非空约束
SET SERVEROUT ON
BEGIN
FOR I IN (select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION from dba_constraints where TABLE_NAME='&table_name' and owner='&username' and CONSTRAINT_TYPE='C') LOOP
IF INSTR(I.SEARCH_CONDITION , 'IS NOT NULL')>0 THEN
DBMS_OUTPUT.PUT_LINE(I.OWNER||' '||I.CONSTRAINT_NAME||' '||I.CONSTRAINT_TYPE||' '||I.TABLE_NAME||' '||I.SEARCH_CONDITION );
END IF;
END LOOP;
END;
/
2、oracle查询当前用户表的非空约束
SET SERVEROUT ON
BEGIN
FOR I IN (select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION from USER_constraints where CONSTRAINT_TYPE='C') LOOP
IF INSTR(I.SEARCH_CONDITION , 'IS NOT NULL')>0 THEN
DBMS_OUTPUT.PUT_LINE(I.OWNER||' '||I.CONSTRAINT_NAME||' '||I.CONSTRAINT_TYPE||' '||I.TABLE_NAME||' '||I.SEARCH_CONDITION );
END IF;
END LOOP;
END;
/
3、oracle查询表相关的约束
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION from dba_constraints where TABLE_NAME='&table_name' and owner='&username' ;
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME,STATUS,INDEX_OWNER,INDEX_NAME
from dba_constraints where TABLE_NAME='&table_name' and owner='&username';