SET SERVEROUTPUT ON
ACCEPT ROW_ID PROMPT 'INPUT A Sring :'
DECLARE
SQL_STMT VARCHAR2(200);
V_TBLNAME VARCHAR2(30);
V_COLNAME VARCHAR2(30);
REC_COUNT NUMBER(7):=0;
CURSOR WT_TABLES IS
SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE DATA_TYPE='VARCHAR2'
ORDER BY TABLE_NAME,COLUMN_NAME;
BEGIN
FOR COLREC IN WT_TABLES LOOP
V_TBLNAME:= COLREC.TABLE_NAME;
V_COLNAME:= COLREC.COLUMN_NAME;
-- DBMS_OUTPUT.PUT_LINE (V_TBLNAME);
SQL_STMT:='SELECT COUNT(*) FROM '||COLREC.TABLE_NAME||' WHERE '||COLREC.COLUMN_NAME||' LIKE :1';
EXECUTE IMMEDIATE SQL_STMT INTO REC_COUNT USING '&ROW_ID';
-- DBMS_OUTPUT.PUT_LINE (V_TBLNAME||' '||REC_COUNT);
IF REC_COUNT>=1 THEN
DBMS_OUTPUT.PUT_LINE ('TABLE: '||
V_TBLNAME||' ,COLUMN: '||V_COLNAME);
END IF;
END LOOP;
END;
/
保存为xx.sql,在DB中运行
ACCEPT ROW_ID PROMPT 'INPUT A Sring :'
DECLARE
SQL_STMT VARCHAR2(200);
V_TBLNAME VARCHAR2(30);
V_COLNAME VARCHAR2(30);
REC_COUNT NUMBER(7):=0;
CURSOR WT_TABLES IS
SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE DATA_TYPE='VARCHAR2'
ORDER BY TABLE_NAME,COLUMN_NAME;
BEGIN
FOR COLREC IN WT_TABLES LOOP
V_TBLNAME:= COLREC.TABLE_NAME;
V_COLNAME:= COLREC.COLUMN_NAME;
-- DBMS_OUTPUT.PUT_LINE (V_TBLNAME);
SQL_STMT:='SELECT COUNT(*) FROM '||COLREC.TABLE_NAME||' WHERE '||COLREC.COLUMN_NAME||' LIKE :1';
EXECUTE IMMEDIATE SQL_STMT INTO REC_COUNT USING '&ROW_ID';
-- DBMS_OUTPUT.PUT_LINE (V_TBLNAME||' '||REC_COUNT);
IF REC_COUNT>=1 THEN
DBMS_OUTPUT.PUT_LINE ('TABLE: '||
V_TBLNAME||' ,COLUMN: '||V_COLNAME);
END IF;
END LOOP;
END;
/
保存为xx.sql,在DB中运行