oracle查索引的sql语句,Oracle中检查外键是否有索引的SQL脚本分享

代码如下:

403_4@

COLUMN COLUMNS format a30 word_wrapped @H_403_4@

COLUMN tablename format a15 word_wrapped @H_403_4@

COLUMN constraint_name format a15 word_wrapped @H_403_4@

SELECT TABLE_NAME,@H_403_4@

CONSTRAINT_NAME,@H_403_4@

CNAME1 || NVL2(CNAME2,',' || CNAME2,NULL) || @H_403_4@

NVL2(CNAME3,' || CNAME3,NULL) || @H_403_4@

NVL2(CNAME4,' || CNAME4,NULL) || @H_403_4@

NVL2(CNAME5,' || CNAME5,NULL) || @H_403_4@

NVL2(CNAME6,' || CNAME6,NULL) || @H_403_4@

NVL2(CNAME7,' || CNAME7,NULL) || @H_403_4@

NVL2(CNAME8,' || CNAME8,NULL) COLUMNS @H_403_4@

FROM (SELECT B.TABLE_NAME,@H_403_4@

B.CONSTRAINT_NAME,@H_403_4@

MAX(DECODE(POSITION,1,COLUMN_NAME,NULL)) CNAME1,2,NULL)) CNAME2,3,NULL)) CNAME3,4,NULL)) CNAME4,5,NULL)) CNAME5,6,NULL)) CNAME6,7,NULL)) CNAME7,8,NULL)) CNAME8,@H_403_4@

COUNT(*) COL_CNT @H_403_4@

FROM (SELECT SUBSTR(TABLE_NAME,30) TABLE_NAME,@H_403_4@

SUBSTR(CONSTRAINT_NAME,30) CONSTRAINT_NAME,@H_403_4@

SUBSTR(COLUMN_NAME,30) COLUMN_NAME,@H_403_4@

POSITION @H_403_4@

FROM USER_CONS_COLUMNS) A,@H_403_4@

USER_CONSTRAINTS B @H_403_4@

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME @H_403_4@

AND B.CONSTRAINT_TYPE = 'R' @H_403_4@

GROUP BY B.TABLE_NAME,B.CONSTRAINT_NAME) CONS @H_403_4@

WHERE COL_CNT > ALL @H_403_4@

(SELECT COUNT(*) @H_403_4@

FROM USER_IND_COLUMNS I @H_403_4@

WHERE I.TABLE_NAME = CONS.TABLE_NAME @H_403_4@

AND I.COLUMN_NAME IN (CNAME1,CNAME2,CNAME3,CNAME4,CNAME5,@H_403_4@

CNAME6,CNAME7,CNAME8) @H_403_4@

AND I.COLUMN_POSITION <= CONS.COL_CNT @H_403_4@

GROUP BY I.INDEX_NAME) @H_403_4@

/@H_403_4@@H_403_4@

在上面的基础上修改了一下，可以检查所有的用户。@H_403_4@

代码如下:

403_4@

SET linesize 400;@H_403_4@

COLUMN OWNER format a10 word_wrapped @H_403_4@

COLUMN COLUMNS format a30 word_wrapped @H_403_4@

COLUMN TABLE_NAME format a15 word_wrapped @H_403_4@

COLUMN CONSTRAINT_NAME format a40 word_wrapped @H_403_4@

SELECT OWNER,@H_403_4@

TABLE_NAME,NULL) COLUMNS @H_403_4@

FROM (SELECT B.OWNER,B.TABLE_NAME,@H_403_4@

POSITION @H_403_4@

FROM DBA_CONS_COLUMNS WHERE OWNER NOT IN ('SYS','SYSTEM','SYSMAN','HR','OE','EXFSYS','DBSNMP','MDSYS','OLAPSYS','SCOTT','SH','PM','CTXSYS')) A,@H_403_4@

DBA_CONSTRAINTS B @H_403_4@

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME @H_403_4@

AND B.CONSTRAINT_TYPE = 'R' @H_403_4@

GROUP BY B.OWNER,B.CONSTRAINT_NAME) CONS @H_403_4@

WHERE COL_CNT > ALL @H_403_4@

(SELECT COUNT(*) @H_403_4@

FROM DBA_IND_COLUMNS I @H_403_4@

WHERE I.TABLE_NAME = CONS.TABLE_NAME AND I.TABLE_OWNER=CONS.OWNER@H_403_4@

AND I.COLUMN_NAME IN (CNAME1,CNAME8) @H_403_4@

AND I.COLUMN_POSITION <= CONS.COL_CNT @H_403_4@

GROUP BY I.INDEX_NAME) @H_403_4@

/@H_403_4@

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。