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)
/
热门推荐
10 八一幼儿祝福语大全简短
11 公司乔迁食堂祝福语简短
12 婚礼结束聚餐祝福语简短
13 儿媳买车妈妈祝福语简短
14 毕业送礼老师祝福语简短
15 同事辞职正常祝福语简短
16 恭贺新婚文案祝福语简短
17 金店立秋祝福语简短英文
18 婆婆高寿祝福语大全简短