Oracle中检查外键是否有索引的SQL脚本分享
COLUMNCOLUMNSformata30word_wrapped
COLUMNtablenameformata15word_wrapped
COLUMNconstraint_nameformata15word_wrapped
SELECTTABLE_NAME,
CONSTRAINT_NAME,
CNAME1||NVL2(CNAME2,','||CNAME2,NULL)||
NVL2(CNAME3,','||CNAME3,NULL)||
NVL2(CNAME4,','||CNAME4,NULL)||
NVL2(CNAME5,','||CNAME5,NULL)||
NVL2(CNAME6,','||CNAME6,NULL)||
NVL2(CNAME7,','||CNAME7,NULL)||
NVL2(CNAME8,','||CNAME8,NULL)COLUMNS
FROM(SELECTB.TABLE_NAME,
B.CONSTRAINT_NAME,
MAX(DECODE(POSITION,1,COLUMN_NAME,NULL))CNAME1,
MAX(DECODE(POSITION,2,COLUMN_NAME,NULL))CNAME2,
MAX(DECODE(POSITION,3,COLUMN_NAME,NULL))CNAME3,
MAX(DECODE(POSITION,4,COLUMN_NAME,NULL))CNAME4,
MAX(DECODE(POSITION,5,COLUMN_NAME,NULL))CNAME5,
MAX(DECODE(POSITION,6,COLUMN_NAME,NULL))CNAME6,
MAX(DECODE(POSITION,7,COLUMN_NAME,NULL))CNAME7,
MAX(DECODE(POSITION,8,COLUMN_NAME,NULL))CNAME8,
COUNT(*)COL_CNT
FROM(SELECTSUBSTR(TABLE_NAME,1,30)TABLE_NAME,
SUBSTR(CONSTRAINT_NAME,1,30)CONSTRAINT_NAME,
SUBSTR(COLUMN_NAME,1,30)COLUMN_NAME,
POSITION
FROMUSER_CONS_COLUMNS)A,
USER_CONSTRAINTSB
WHEREA.CONSTRAINT_NAME=B.CONSTRAINT_NAME
ANDB.CONSTRAINT_TYPE='R'
GROUPBYB.TABLE_NAME,B.CONSTRAINT_NAME)CONS
WHERECOL_CNT>ALL
(SELECTCOUNT(*)
FROMUSER_IND_COLUMNSI
WHEREI.TABLE_NAME=CONS.TABLE_NAME
ANDI.COLUMN_NAMEIN(CNAME1,CNAME2,CNAME3,CNAME4,CNAME5,
CNAME6,CNAME7,CNAME8)
ANDI.COLUMN_POSITION<=CONS.COL_CNT
GROUPBYI.INDEX_NAME)
/
在上面的基础上修改了一下，可以检查所有的用户。
SETlinesize400;
COLUMNOWNERformata10word_wrapped
COLUMNCOLUMNSformata30word_wrapped
COLUMNTABLE_NAMEformata15word_wrapped
COLUMNCONSTRAINT_NAMEformata40word_wrapped
SELECTOWNER,
TABLE_NAME,
CONSTRAINT_NAME,
CNAME1||NVL2(CNAME2,','||CNAME2,NULL)||
NVL2(CNAME3,','||CNAME3,NULL)||
NVL2(CNAME4,','||CNAME4,NULL)||
NVL2(CNAME5,','||CNAME5,NULL)||
NVL2(CNAME6,','||CNAME6,NULL)||
NVL2(CNAME7,','||CNAME7,NULL)||
NVL2(CNAME8,','||CNAME8,NULL)COLUMNS
FROM(SELECTB.OWNER,B.TABLE_NAME,
B.CONSTRAINT_NAME,
MAX(DECODE(POSITION,1,COLUMN_NAME,NULL))CNAME1,
MAX(DECODE(POSITION,2,COLUMN_NAME,NULL))CNAME2,
MAX(DECODE(POSITION,3,COLUMN_NAME,NULL))CNAME3,
MAX(DECODE(POSITION,4,COLUMN_NAME,NULL))CNAME4,
MAX(DECODE(POSITION,5,COLUMN_NAME,NULL))CNAME5,
MAX(DECODE(POSITION,6,COLUMN_NAME,NULL))CNAME6,
MAX(DECODE(POSITION,7,COLUMN_NAME,NULL))CNAME7,
MAX(DECODE(POSITION,8,COLUMN_NAME,NULL))CNAME8,
COUNT(*)COL_CNT
FROM(SELECTSUBSTR(TABLE_NAME,1,30)TABLE_NAME,
SUBSTR(CONSTRAINT_NAME,1,30)CONSTRAINT_NAME,
SUBSTR(COLUMN_NAME,1,30)COLUMN_NAME,
POSITION
FROMDBA_CONS_COLUMNSWHEREOWNERNOTIN('SYS','SYSTEM','SYSMAN','HR','OE','EXFSYS','DBSNMP','MDSYS','OLAPSYS','SCOTT','EXFSYS','SH','PM','CTXSYS'))A,
DBA_CONSTRAINTSB
WHEREA.CONSTRAINT_NAME=B.CONSTRAINT_NAME
ANDB.CONSTRAINT_TYPE='R'
GROUPBYB.OWNER,B.TABLE_NAME,B.CONSTRAINT_NAME)CONS
WHERECOL_CNT>ALL
(SELECTCOUNT(*)
FROMDBA_IND_COLUMNSI
WHEREI.TABLE_NAME=CONS.TABLE_NAMEANDI.TABLE_OWNER=CONS.OWNER
ANDI.COLUMN_NAMEIN(CNAME1,CNAME2,CNAME3,CNAME4,CNAME5,
CNAME6,CNAME7,CNAME8)
ANDI.COLUMN_POSITION<=CONS.COL_CNT
GROUPBYI.INDEX_NAME)
/