SPOOL chkfkidx.log
SET ECHO ON
DROP TABLE temp_chkfkidx
/
CREATE TABLE temp_chkfkidx (
table_name VARCHAR2(30),
fk_table_column VARCHAR2(61),
status VARCHAR2(15))
/
DELETE FROM temp_chkfkidx
/
DROP TABLE temp_crfkidx
/
CREATE TABLE temp_crfkidx (
fk_table_name VARCHAR2(30),
fk_column_name VARCHAR2(30),
sql_stmt VARCHAR2(100))
/
DELETE FROM temp_crfkidx
/
DECLARE
v_yes_no CHAR(1) := 'N';
CURSOR c_table IS
SELECT ut.table_name
FROM user_tables ut
ORDER BY ut.table_name;
CURSOR c_constraint (i_table_name VARCHAR2) IS
SELECT uc1.column_name pk_column_name,
uc2.table_name, uc2.column_name
FROM user_cons_columns uc1, user_cons_columns uc2,
user_constraints ucn
WHERE uc1.table_name = i_table_name
AND uc1.constraint_name = ucn.r_constraint_name
AND ucn.constraint_name = uc2.constraint_name
ORDER BY uc1.column_name, uc2.table_name, uc2.column_name;
CURSOR c_index (i_table_name VARCHAR2, i_column_name VARCHAR2) IS
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
FROM user_ind_columns uic
WHERE uic.table_name = i_table_name
AND uic.column_name = i_column_name;
BEGIN
FOR r_tab IN c_table LOOP
FOR r_con IN c_constraint (r_tab.table_name) LOOP
OPEN c_index (r_con.table_name, r_con.column_name);
FETCH c_index INTO v_yes_no;
CLOSE c_index;
IF v_yes_no = 'Y' THEN
INSERT INTO temp_chkfkidx (
table_name, fk_table_column, status)
VALUES (r_tab.table_name,
r_con.table_name || '.' || r_con.column_name, 'Indexed');
ELSE
INSERT INTO temp_chkfkidx (
table_name, fk_table_column, status)
VALUES (r_tab.table_name,
r_con.table_name || '.' || r_con.column_name, 'NOT indexed');
INSERT INTO temp_crfkidx (
fk_table_name, fk_column_name, sql_stmt)
VALUES (r_con.table_name, r_con.column_name,
'CREATE INDEX FK_' ||
r_con.table_name || '_' || r_con.column_name ||
CHR(10) || ' ON ' ||
r_con.table_name || ' (' || r_con.column_name || ');');
END IF;
END LOOP;
END LOOP;
END;
/
SPOOL OFF
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 78
SET NEWPAGE 0
SET PAGESIZE 60
SPOOL chkfkidx.lst
COLUMN fk_table_column FORMAT A45 WORD_WRAP HEADING 'Foreign Key
Table.Column' COLUMN status FORMAT A12 WORD_WRAP HEADING 'Status' COLUMN
table_name FORMAT A18 WORD_WRAP HEADING 'Primary Key|Table' BREAK ON
table_name SKIP 1
TTITLE 'Foreign Key Index Audit'
SELECT table_name, fk_table_column, status
FROM temp_chkfkidx
ORDER BY table_name, fk_table_column
/
TTITLE OFF
CLEAR BREAKS
CLEAR COLUMNS
SET LINESIZE 100
SET NEWPAGE 1
SPOOL OFF
SPOOL crfkidx.sql
SET PAGESIZE 0
SELECT sql_stmt
FROM temp_crfkidx
ORDER BY fk_table_name, fk_column_name
/
SET FEEDBACK ON
SET LINESIZE 78
SET PAGESIZE 24
SPOOL OFF
|