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)
/
热门推荐
1 破五祝福语简短的
10 集体年会祝福语简短创意
11 考上大学简短祝福语、
12 精简祝福语简短小故事
13 退休祝福语简短独特老师
14 对老人新年祝福语简短
15 朝鲜元旦祝福语大全简短
16 9字祝福语简短创意
17 送女儿礼物祝福语简短
18 鼠年同事祝福语简短创意