在整个模式中搜索字符串 [英] Searching for string in the whole schema

问题描述

我有许多模式的Oracle数据库.我想在一种特定模式下的所有表中找到xyz字符串.我已尝试按照此处接受的答案进行操作(

I have Oracle database with many schemas. I'd like to find xyz string in all the tables in one specific schema. I've tried to do it as in accepted answer here (Search All Fields In All Tables For A Specific Value (Oracle)). Unfortunately, I get error:

3 ORA-00903: invalid table name
ORA-06512: at line 8 SQL.sql 30 2

第8行是

SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||

如何进行完整搜索?我也尝试了其他谷歌搜索解决方案，但没有一个对我有用.

How can I do this full search? I've also tried other googled solutions but none of them worked for me.

推荐答案

从其他答案改编后，运行以下查询:

Adapted from the other answer, run this query:

SELECT
    'SELECT '''|| owner||table_name||column_name|| ''' as locn, COUNT(*) FROM "' || owner || '"."' || table_name ||'" WHERE "'||column_name||'" = ''[VALUE YOURE LOOKING FOR HERE]'' UNION ALL'
FROM
  all_tab_columns
WHERE owner <> 'SYS' and data_type LIKE '%CHAR%'

用所需的值替换 [您正在寻找的值] .也更换方括号.请勿触摸任何撇号.

Replace the [VALUE YOURE LOOKING FOR HERE] with the value you're looking for. Replace the square brackets too. Do not touch any apostrophes.

然后运行查询，它将产生大量的sql语句.将它们复制到查询工具结果网格之外，将其粘贴到查询区域面板中，删除所有的最后一个并集，然后运行它们.等一下很长一段时间了.最终，它将生成每个表和列名称的列表，以及您要查找的值出现在该列中的次数计数

Then run the query, and it'll produce a huge number of sql statements. Copy them out of your query tool results grid, paste them into the query area panel, delete the last union all and then run them. And wait. For a very long time. Eventually it'll produce a list of every table and column name, together with the count of the number of times that value you're looking for appears in that column

这篇关于在整个模式中搜索字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！