Oracle搜索所有表查找关键字,根据关键字查询oracle中所有表的记录

由于业务需要，根据关键字，查询所有表中所有字段包含该关键字的记录数，方便系统维护中核查数据。直接上代码，同行指正。

CREATE OR REPLACE PROCEDURE PM_QUERY_KEY_WORD_INFO (

V_KEY_WORD IN VARCHAR2

) AS

-- SQL STRING

V_SQL VARCHAR2(4000);

-- FIELD OF TABLE

V_TB_COL VARCHAR2(512);

-- RECORD NUMBER

V_CNT NUMBER(18,0);

CURSOR CUR_SQLS IS

SELECT 'SELECT ''' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ''' AS COL_NAME, NVL(COUNT(' || X.COLUMN_NAME || '),0) AS CNT FROM ' || X.TABLE_NAME

|| ' A WHERE A.' || X.COLUMN_NAME || ' LIKE ''%' || V_KEY_WORD || '%''' AS SQL_STR

FROM COLS X JOIN USER_OBJECTS Y ON X.TABLE_NAME = Y.OBJECT_NAME

WHERE Y.TEMPORARY <> 'Y' AND OBJECT_TYPE = 'TABLE'

AND X.DATA_TYPE IN ('CHAR','VARCHAR2','VARCHAR