在一些表中有成百上千的表具有成千上万的行,因此我知道这可能需要很长的查询时间。但是我唯一知道的是,我要查询的字段的值是
1/22/2008P09RR8
。<
我尝试使用以下语句根据以下内容找到合适的列我认为应该命名的名称,但未返回任何结果。
SELECT * from dba_objects
WHERE object_name like '%DTN%'
该数据库上绝对没有文档,而且我也不知道该字段是从哪里提取的。
有什么想法吗?
#1 楼
Quote:我尝试在下面使用此语句
根据我认为应该命名的名称找到合适的列
没有结果。*
SELECT * from dba_objects WHERE
object_name like '%DTN%'
列不是对象。如果您希望列名称类似于'%DTN%',则所需查询为:
SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
但是如果'DTN'字符串只是您的猜测可能无济于事。
顺便问一下,您如何确定'1/22 / 2008P09RR8'是直接从单个列中选择的值?如果您根本不知道它来自何处,则可能是几列的串联,或者是某些函数的结果,或者是嵌套表对象中的值。因此,您可能会大吃一惊,试图检查该值的每一列。您不能先从任何显示此值的客户端应用程序开始,然后尝试找出要使用哪个查询来获取该值吗?每个表的值。您还可以使用PL / SQL块和动态SQL在一个SQL会话中完全完成类似的工作。以下是一些草草编写的代码:
SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
BEGIN
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING '1/22/2008P09RR8';
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
也有一些方法可以使它更有效。
给定您要查找的值,您可以清楚地消除NUMBER或DATE类型的任何列,这将减少查询的数量。甚至可以将其限制为类型为'%CHAR%'的列。
您可以为每个表构建一个查询,而不是每列一个查询,如下所示:
SELECT * FROM table1
WHERE column1 = 'value'
OR column2 = 'value'
OR column3 = 'value'
...
;
评论
您应该将其限制为char,varchar和varchar2列,因为number和date列可能无法包含该字符串。
– Erich Kitzmueller
09年10月10日在18:54
@ammoQ-就像我在倒数第二段中所说的那样?
–戴夫·科斯塔(Dave Costa)
09年11月11日在16:31
我在9i上运行此命令,但出现column_name未知错误。有人可以告诉我在9i上运行此程序需要进行哪些修改吗?
– Regmi
2012年5月4日,0:23
@Regmi-抱歉,这实际上是我的代码中的错误,而不是版本问题。该循环应该由all_tab_columns而非all_tables驱动。我已经解决了。
–戴夫·科斯塔(Dave Costa)
2012年5月4日12:08
@DaveCosta-感谢您修复,但是我仍然在第6行收到“表或视图不存在”错误。第6行是“立即执行”。
– Regmi
2012年5月7日18:27
#2 楼
我对上面的代码做了一些修改,以使其在只搜索一个所有者的情况下可以更快地运行。您只需要更改3个变量v_owner,v_data_type和v_search_string以适合您要搜索的内容。
SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
-- Type the owner of the tables you are looking at
v_owner VARCHAR2(255) :='ENTER_USERNAME_HERE';
-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
v_search_string VARCHAR2(4000) :='string to search here...';
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
评论
为了能够运行此查询,我不得不注释第一行。另外,我无法删除所有者过滤器并运行查询。
–波帕·安德烈(Popa Andrei)
16年8月18日在7:28
我需要在表名/列名两边加上双引号,以避免出现以下问题:'SELECT COUNT(*)FROM“'|| t.table_name ||'” WHERE“'|| t.column_name | |'“ =:1'
–史蒂夫·钱伯斯(Steve Chambers)
17年9月13日在15:00
请注意,尽管名称如此,all_tab_cols也包含视图
– phil_w
19-11-13在20:52
dbms_output到底是什么?因为查询已在DataGrip中成功执行,但是此后没有看到任何结果。
– misteeque
2月25日上午10:29
我知道这有点旧,但是当我运行它时,我只会得到“匿名块完成”的脚本输出
– JasonWH
6月30日16:22
#3 楼
我知道这是一个老话题。但是我看到一个问题的评论,询问是否可以在SQL
中完成而不是使用PL/SQL
。因此,请考虑发布解决方案。下面的演示是在整个SCHEMA中的所有表的所有列中搜索值:
搜索字符类型
让我们在
KING
模式中查找值SCOTT
。SQL> variable val varchar2(10)
SQL> exec :val := 'KING'
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
KING EMP ENAME
SQL>
搜索NUMERIC类型
让我们在
20
模式中查找值SCOTT
。SQL> variable val NUMBER
SQL> exec :val := 20
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
20 DEPT DEPTNO
20 EMP DEPTNO
20 EMP HIREDATE
20 SALGRADE HISAL
20 SALGRADE LOSAL
SQL>
评论
hrmm...。使用xml看起来有些矫kill过正。此外:XML处理中发生错误ORA-00932:数据类型不一致:预期的编号为BLOB
–towi
15年2月12日在8:40
ORA-19202:XML处理中发生错误ORA-00932:数据类型不一致:预期的CHAR获得BLOB ORA-06512:在“ SYS.DBMS_XMLGEN”行288中ORA-06512:在行119202。00000-“在XML处理中发生错误%s“ *原因:处理XML函数时发生错误*操作:检查给定的错误消息并解决相应的问题
–穆罕默德·费萨尔(Mohammad Faisal)
17 Mar 28 '17在5:47
有任何想法吗? ORA-19202:XML处理中发生错误ORA-22813:操作数值超出系统限制ORA-06512:位于“ SYS.DBMS_XMLGEN”的第288行ORA-06512:位于第1行
–最佳素数
17年6月8日在10:09
#4 楼
是的,您可以,您的DBA会讨厌您,并且会发现您不满意,因为这会导致大量I / O,并且随着高速缓存的清除,数据库性能会真正下降。select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;
首先。
我将从运行查询开始,使用
v$session
和v$sqlarea
。这根据oracle版本而变化。这样会缩小空间,不会碰到任何东西。#5 楼
这是另一个比较版本,将比较较低的子字符串匹配。这适用于Oracle 11g。DECLARE
match_count INTEGER;
-- Type the owner of the tables you are looking at
v_owner VARCHAR2(255) :='OWNER_NAME';
-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
v_search_string VARCHAR2(4000) :='%lower-search-sub-string%';
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE lower('||t.column_name||') like :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
#6 楼
我会做这样的事情(生成您需要的所有选择)。您以后可以将它们提供给sqlplus:
echo "select table_name from user_tables;" | sqlplus -S user/pwd | grep -v "^--" | grep -v "TABLE_NAME" | grep "^[A-Z]" | while read sw;
do echo "desc $sw" | sqlplus -S user/pwd | grep -v "\-\-\-\-\-\-" | awk -F' ' '{print }' | while read nw;
do echo "select * from $sw where $nw='val'";
done;
done;
它产生:
/>
select * from TBL1 where DESCRIPTION='val'
select * from TBL1 where ='val'
select * from TBL2 where Name='val'
select * from TBL2 where LNG_ID='val'
它的作用是-为
table_name
中的每个user_tables
获取每个字段(来自desc)并从表中创建一个select *,其中字段等于“ val”。#7 楼
我修改了Flood脚本,使其对每个表执行一次,而不是对每个表的每一列执行一次,以提高执行速度。它需要Oracle 11g或更高版本。 set serveroutput on size 100000
declare
v_match_count integer;
v_counter integer;
-- The owner of the tables to search through (case-sensitive)
v_owner varchar2(255) := 'OWNER_NAME';
-- A string that is part of the data type(s) of the columns to search through (case-insensitive)
v_data_type varchar2(255) := 'CHAR';
-- The string to be searched for (case-insensitive)
v_search_string varchar2(4000) := 'FIND_ME';
-- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL
v_sql clob := '';
begin
for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in
(select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' || upper(v_data_type) || '%')
order by table_name) loop
v_counter := 0;
v_sql := '';
for cur_columns in (select column_name from all_tab_columns where
owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
if v_counter > 0 then
v_sql := v_sql || ' or ';
end if;
v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
v_counter := v_counter + 1;
end loop;
v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql;
execute immediate v_sql
into v_match_count;
if v_match_count > 0 then
dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
end if;
end loop;
exception
when others then
dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));
end;
/
#8 楼
我在@Lalit Kumars答案中遇到以下问题,ORA-19202: Error occurred in XML processing
ORA-00904: "SUCCESS": invalid identifier
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
解决方法是:
WITH char_cols AS
(SELECT /*+materialize */ table_name, column_name
FROM cols
WHERE data_type IN ('CHAR', 'VARCHAR2'))
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
SUBSTR (table_name, 1, 14) "Table",
SUBSTR (column_name, 1, 14) "Column"
FROM char_cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
|| column_name
|| '" from "'
|| table_name
|| '" where upper("'
|| column_name
|| '") like upper(''%'
|| :val
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER BY "Table"
/
#9 楼
如果我们知道表和列的名称,但想找出每个模式出现的字符串的次数:Declare
owner VARCHAR2(1000);
tbl VARCHAR2(1000);
cnt number;
ct number;
str_sql varchar2(1000);
reason varchar2(1000);
x varchar2(1000):='%string_to_be_searched%';
cursor csr is select owner,table_name
from all_tables where table_name ='table_name';
type rec1 is record (
ct VARCHAR2(1000));
type rec is record (
owner VARCHAR2(1000):='',
table_name VARCHAR2(1000):='');
rec2 rec;
rec3 rec1;
begin
for rec2 in csr loop
--str_sql:= 'select count(*) from '||rec.owner||'.'||rec.table_name||' where CTV_REMARKS like '||chr(39)||x||chr(39);
--dbms_output.put_line(str_sql);
--execute immediate str_sql
execute immediate 'select count(*) from '||rec2.owner||'.'||rec2.table_name||' where column_name like '||chr(39)||x||chr(39)
into rec3;
if rec3.ct <> 0 then
dbms_output.put_line(rec2.owner||','||rec3.ct);
else null;
end if;
end loop;
end;
#10 楼
搜索整个数据库的过程: CREATE or REPLACE PROCEDURE SEARCH_DB(SEARCH_STR IN VARCHAR2, TAB_COL_RECS OUT VARCHAR2) IS
match_count integer;
qry_str varchar2(1000);
CURSOR TAB_COL_CURSOR IS
SELECT TABLE_NAME,COLUMN_NAME,OWNER,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE DATA_TYPE in ('NUMBER','VARCHAR2') AND OWNER='SCOTT';
BEGIN
FOR TAB_COL_REC IN TAB_COL_CURSOR
LOOP
qry_str := 'SELECT COUNT(*) FROM '||TAB_COL_REC.OWNER||'.'||TAB_COL_REC.TABLE_NAME||
' WHERE '||TAB_COL_REC.COLUMN_NAME;
IF TAB_COL_REC.DATA_TYPE = 'NUMBER' THEN
qry_str := qry_str||'='||SEARCH_STR;
ELSE
qry_str := qry_str||' like '||SEARCH_STR;
END IF;
--dbms_output.put_line( qry_str );
EXECUTE IMMEDIATE qry_str INTO match_count;
IF match_count > 0 THEN
dbms_output.put_line( qry_str );
--dbms_output.put_line( TAB_COL_REC.TABLE_NAME ||' '||TAB_COL_REC.COLUMN_NAME ||' '||match_count);
TAB_COL_RECS := TAB_COL_RECS||'@@'||TAB_COL_REC.TABLE_NAME||'##'||TAB_COL_REC.COLUMN_NAME;
END IF;
END LOOP;
END SEARCH_DB;
执行语句
DECLARE
SEARCH_STR VARCHAR2(200);
TAB_COL_RECS VARCHAR2(200);
BEGIN
SEARCH_STR := 10;
SEARCH_DB(
SEARCH_STR => SEARCH_STR,
TAB_COL_RECS => TAB_COL_RECS
);
DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS);
END;
样本结果
Connecting to the database test.
SELECT COUNT(*) FROM SCOTT.EMP WHERE DEPTNO=10
SELECT COUNT(*) FROM SCOTT.DEPT WHERE DEPTNO=10
TAB_COL_RECS = @@EMP##DEPTNO@@DEPT##DEPTNO
Process exited.
Disconnecting from the database test.
#11 楼
我没有关于SQL提示的简单解决方案。但是,有很多工具,例如toad和PL / SQL Developer,它们具有GUI,用户可以在其中输入要搜索的字符串,并且它将返回找到该字符串的表/过程/对象。#12 楼
有一些免费的工具可以进行此类搜索,例如,该工具可以正常工作并且可以使用源代码:https://sites.google.com/site/freejansoft/dbsearch
您需要Oracle ODBC驱动程序和DSN才能使用此工具。
#13 楼
修改代码以使用LIKE查询而不区分大小写地搜索,而不是查找完全匹配的内容...DECLARE
match_count INTEGER;
-- Type the owner of the tables you want to search.
v_owner VARCHAR2(255) :='USER';
-- Type the data type you're looking for (in CAPS). Examples include: VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking for.
v_search_string VARCHAR2(4000) :='Test';
BEGIN
dbms_output.put_line( 'Starting the search...' );
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE LOWER('||t.column_name||') LIKE :1'
INTO match_count
USING LOWER('%'||v_search_string||'%');
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
#14 楼
-运行完成-没有错误 SET SERVEROUTPUT ON SIZE 100000
DECLARE
v_match_count INTEGER;
v_counter INTEGER;
v_owner VARCHAR2 (255) := 'VASOA';
v_search_string VARCHAR2 (4000) := '99999';
v_data_type VARCHAR2 (255) := 'CHAR';
v_sql CLOB := '';
BEGIN
FOR cur_tables
IN ( SELECT owner, table_name
FROM all_tables
WHERE owner = v_owner
AND table_name IN (SELECT table_name
FROM all_tab_columns
WHERE owner = all_tables.owner
AND data_type LIKE
'%'
|| UPPER (v_data_type)
|| '%')
ORDER BY table_name)
LOOP
v_counter := 0;
v_sql := '';
FOR cur_columns
IN (SELECT column_name, table_name
FROM all_tab_columns
WHERE owner = v_owner
AND table_name = cur_tables.table_name
AND data_type LIKE '%' || UPPER (v_data_type) || '%')
LOOP
IF v_counter > 0
THEN
v_sql := v_sql || ' or ';
END IF;
IF cur_columns.column_name is not null
THEN
v_sql :=
v_sql
|| 'upper('
|| cur_columns.column_name
|| ') ='''
|| UPPER (v_search_string)||'''';
v_counter := v_counter + 1;
END IF;
END LOOP;
IF v_sql is null
THEN
v_sql :=
'select count(*) from '
|| v_owner
|| '.'
|| cur_tables.table_name;
END IF;
IF v_sql is not null
THEN
v_sql :=
'select count(*) from '
|| v_owner
|| '.'
|| cur_tables.table_name
|| ' where '
|| v_sql;
END IF;
--v_sql := 'select count(*) from ' ||v_owner||'.'|| cur_tables.table_name ||' where '|| v_sql;
--dbms_output.put_line(v_sql);
--DBMS_OUTPUT.put_line (v_sql);
EXECUTE IMMEDIATE v_sql INTO v_match_count;
IF v_match_count > 0
THEN
DBMS_OUTPUT.put_line (v_sql);
dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error when executing the following: '
|| DBMS_LOB.SUBSTR (v_sql, 32600));
END;
/
#15 楼
从此Blog帖子中借用,稍微增强和简化以下简单的SQL语句似乎可以很好地完成工作:SELECT DISTINCT (:val) "Search Value", TABLE_NAME "Table", COLUMN_NAME "Column"
FROM cols,
TABLE (XMLSEQUENCE (DBMS_XMLGEN.GETXMLTYPE(
'SELECT "' || COLUMN_NAME || '" FROM "' || TABLE_NAME || '" WHERE UPPER("'
|| COLUMN_NAME || '") LIKE UPPER(''%' || :val || '%'')' ).EXTRACT ('ROWSET/ROW/*')))
ORDER BY "Table";
#16 楼
SELECT * from all_objects WHERE object_name like '%your_string%';
评论
我们可以使用单个查询而不是使用存储过程来做到这一点吗?是的,可以在纯SQL中完成。查看SQL以在整个SCHEMA中的所有表的所有列中搜索值
@LalitKumarB您列出的页面不再可用。是否可以发布一些信息作为答案?
@DodziDzakuma该页面可访问lalitkumarb.wordpress.com/2015/01/06/…另外我已经发布了答案,请向下滚动或查看stackoverflow.com/a/27794127/3989608
如果您在找出Lalit Kumar查询时遇到问题,请尝试以下演示:sqlfiddle.com/#!4/76924c/2/0