主頁 > 知識庫 > Oracle中檢查外鍵是否有索引的SQL腳本分享

Oracle中檢查外鍵是否有索引的SQL腳本分享

熱門標(biāo)簽:七日殺a19.5全地圖標(biāo)注 車瑪仕極限運動場所地圖標(biāo)注 騰訊地圖標(biāo)注要費用嗎 外呼電話系統(tǒng)用卡嗎 廣東營銷智能外呼系統(tǒng)商家 電渠外呼系統(tǒng) 地圖標(biāo)注怎么保存 N個你智能電銷機器人 高德地圖標(biāo)注公司名字大全

復(fù)制代碼 代碼如下:

COLUMN COLUMNS format a30 word_wrapped
COLUMN tablename format a15 word_wrapped
COLUMN constraint_name format a15 word_wrapped
SELECT 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 (SELECT 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 (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
                       POSITION
                  FROM USER_CONS_COLUMNS) A,
               USER_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND B.CONSTRAINT_TYPE = 'R'
         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 WHERE COL_CNT > ALL
 (SELECT COUNT(*)
          FROM USER_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_NAME
           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
                CNAME6, CNAME7, CNAME8)
           AND I.COLUMN_POSITION = CONS.COL_CNT
         GROUP BY I.INDEX_NAME)
/

在上面的基礎(chǔ)上修改了一下,可以檢查所有的用戶。
復(fù)制代碼 代碼如下:

SET linesize 400;
COLUMN OWNER format a10 word_wrapped
COLUMN COLUMNS format a30 word_wrapped
COLUMN TABLE_NAME format a15 word_wrapped
COLUMN CONSTRAINT_NAME format a40 word_wrapped
SELECT OWNER,
     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 (SELECT B.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 (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
                       POSITION
                  FROM DBA_CONS_COLUMNS WHERE OWNER NOT IN ('SYS','SYSTEM','SYSMAN','HR','OE','EXFSYS','DBSNMP','MDSYS','OLAPSYS','SCOTT','EXFSYS','SH','PM','CTXSYS')) A,
               DBA_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND B.CONSTRAINT_TYPE = 'R'
         GROUP BY B.OWNER,B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 WHERE COL_CNT > ALL
 (SELECT COUNT(*)
          FROM DBA_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_NAME AND I.TABLE_OWNER=CONS.OWNER
           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
                CNAME6, CNAME7, CNAME8)
           AND I.COLUMN_POSITION = CONS.COL_CNT
         GROUP BY I.INDEX_NAME)
/

您可能感興趣的文章:
  • 在Oracle數(shù)據(jù)庫中添加外鍵約束的方法詳解
  • Oracle外鍵不加索引引起死鎖示例
  • Oracle系統(tǒng)表外鍵的更名
  • Oracle數(shù)據(jù)庫中外鍵的相關(guān)操作整理

標(biāo)簽:蘇州 玉樹 遼寧 大興安嶺 贛州 來賓 棗莊 長沙

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《Oracle中檢查外鍵是否有索引的SQL腳本分享》,本文關(guān)鍵詞  Oracle,中,檢查,外鍵,是否,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Oracle中檢查外鍵是否有索引的SQL腳本分享》相關(guān)的同類信息!
  • 本頁收集關(guān)于Oracle中檢查外鍵是否有索引的SQL腳本分享的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章