更新时间:2023-12-01 10:44:10
" ALL_TAB_COLUMNS 是一个systable,具有所有表列的数据类型,等等.
"ALL_TAB_COLUMNS" is a systable, have all table columns data type etc.
" USER_TAB_COLUMNS "是一个systable,具有所有表列数据类型,等等(所有者是当前用户). (感谢Rene)
"USER_TAB_COLUMNS" is a systable, have all table columns data type etc (which owner is current user). (Thanks Rene)
示例:
SET SERVEROUTPUT ON SIZE 100000 -- maybe you have a lot of table and columns
DECLARE
matches INTEGER;
BEGIN
FOR columns IN (SELECT table_name, column_name FROM user_tab_columns where data_type = 'VARCHAR2') LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE instr('||t.column_name||' , :1) > 0'
INTO matches
USING 'What you search';
IF matches > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||matches );
END IF;
END LOOP;
END;
/
此查询将输出table_name''column_name''并进行计数,如果您具有标准列和表名,则可以将查询更改为IF列> 0,然后在循环中编写查询UNION并返回游标,或返回表,
this query will output table_name '' column_name'' and count, if you have standart column and table names, you can change query as IF columns > 0 then write a query UNION in loop and return the cursor, or return a table,