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@基本的sql语句有哪些
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@
总结
如果觉得编程之家⽹站内容还不错，欢迎将编程之家⽹站推荐给程序员好友。
本图⽂内容来源于⽹友⽹络收集整理提供，作为学习参考使⽤，版权属于原作者。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。