sql - una - Buscar todos los campos en todas las tablas para un valor específico(Oracle)

buscar una tabla en todas las bases de datos sql

Question

¿Es posible buscar en cada campo de cada tabla un valor particular en Oracle?

Hay cientos de tablas con miles de filas en algunas tablas, así que sé que esto puede llevar mucho tiempo para realizar consultas. Pero lo único que sé es que un valor para el campo que me gustaría consultar es el 1/22/2008P09RR8 . <

Intenté usar esta declaración a continuación para encontrar una columna adecuada en función de lo que creo que debería llamarse pero no arrojó resultados.

SELECT * from dba_objects
WHERE object_name like ''%DTN%''

No hay absolutamente ninguna documentación en esta base de datos y no tengo idea de dónde se extrae este campo.

¿Alguna idea?

Answer 1

--se ejecuta completo - sin error

 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;
/

Answer 2

Aquí hay otra versión modificada que comparará una coincidencia de subcadena inferior. Esto funciona en 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;
/

Answer 3

Citar:

He intentado utilizar esta declaración a continuación para encontrar una columna adecuada en función de lo que creo que debería llamarse pero no arrojó ningún resultado. *

SELECT * from dba_objects WHERE
object_name like ''%DTN%''

Una columna no es un objeto. Si quiere decir que espera que el nombre de columna sea como ''% DTN%'', la consulta que desea es:

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE ''%DTN%'';

Pero si la cadena ''DTN'' es solo una suposición de tu parte, probablemente no ayude.

Por cierto, ¿qué tan seguro está de que ''1/22 ​​/ 2008P09RR8'' sea un valor seleccionado directamente de una sola columna? Si no sabe en absoluto de dónde viene, podría ser una concatenación de varias columnas, o el resultado de alguna función, o un valor sentado en un objeto de tabla anidada. Por lo tanto, podría estar en una búsqueda inútil intentando verificar cada columna por ese valor. ¿No puede comenzar con la aplicación del cliente que muestra este valor y tratar de averiguar qué consulta está utilizando para obtenerlo?

De todos modos, la respuesta de diciu da un método de generación de consultas SQL para verificar cada columna de cada tabla para el valor. También puede hacer cosas similares por completo en una sesión SQL utilizando un bloque PL / SQL y SQL dinámico. Aquí hay un código escrito apresuradamente para eso:

 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;
/

Hay algunas formas en que podrías hacerlo más eficiente también.

En este caso, dado el valor que está buscando, puede eliminar claramente cualquier columna que sea de tipo NÚMERO o FECHA, lo que reduciría el número de consultas. Tal vez incluso restringirlo a columnas donde el tipo es como ''% CHAR%''.

En lugar de una consulta por columna, puede crear una consulta por tabla como esta:

SELECT * FROM table1
WHERE column1 = ''value''
OR column2 = ''value''
OR column3 = ''value''
...
;

Answer 4

Estaba teniendo problemas siguientes para la respuesta de @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

La solución es:

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"
/

Answer 5

Haría algo como esto (genera todas las selecciones que necesita). Luego puede alimentarlos a 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 $1}'' | while read nw;
do echo "select * from $sw where $nw=''val''";
done;
done;

Cede:

select * from TBL1 where DESCRIPTION=''val''
select * from TBL1 where =''val''
select * from TBL2 where Name=''val''
select * from TBL2 where LNG_ID=''val''

Y lo que hace es - para cada user_tables de user_tables obtener cada campo (de desc) y crear un select * from table donde el campo es igual a ''val''.

Answer 6

Hay algunas herramientas gratuitas que hacen este tipo de búsqueda, por ejemplo, esta funciona bien y el código fuente está disponible: https://sites.google.com/site/freejansoft/dbsearch

Necesitará el controlador Oracle ODBC y un DSN para usar esta herramienta.

Answer 7

Modificar el código para buscar sin distinguir entre mayúsculas y minúsculas con una consulta LIKE en lugar de buscar coincidencias exactas ...

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;

Answer 8

Modifiqué el código anterior para que funcione más rápido si está buscando solo un propietario. Solo tiene que cambiar las 3 variables v_owner, v_data_type y v_search_string para que se ajusten a lo que está buscando.

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;
/

Answer 9

Modifiqué la secuencia de comandos de Flood para que se ejecutara una vez por cada tabla en lugar de por cada columna de cada tabla para una ejecución más rápida. Requiere Oracle 11g o superior.

 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;
/

Answer 10

No tengo una solución simple en la fuente de SQL. Sin embargo, hay bastantes herramientas como sapo y Desarrollador PL / SQL que tienen una GUI donde un usuario puede ingresar la cadena que se va a buscar y devolverá la tabla / procedimiento / objeto donde se encuentra.

Answer 11

Procedimiento para buscar en toda la base de datos:

 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;

Ejecutar declaración

 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;

Resultados de muestra

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.

Answer 12

Sé que este es un tema viejo. Pero veo un comentario a la pregunta que pregunta si podría hacerse en SQL en lugar de usar PL/SQL . Así que pensé en publicar una solución.

La demostración a continuación es Buscar un VALOR en todas las COLUMNAS de todas las TABLAS en un SCHEMA completo :

• Buscar un tipo de CARÁCTER

Busquemos el valor KING en el esquema 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>

• Buscar un tipo NUMERIC

Busquemos el valor 20 en el esquema 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>

Answer 13

Sí, puedes y tu DBA te odiará y te buscará para clavar los zapatos en el piso porque eso causará muchas E / S y reducirá el rendimiento de la base de datos a medida que se purga el caché.

select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;

para comenzar.

Comenzaría con las consultas en curso, usando v$session y v$sqlarea . Esto cambia según la versión de Oracle. Esto reducirá el espacio y no golpeará todo.

Answer 14

Tomando prestado, mejorando y simplificando ligeramente de esta publicación del blog, la siguiente instrucción SQL simple parece hacer bastante bien el trabajo:

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";

Answer 15

si conocemos los nombres de tablas y columnas, pero queremos averiguar el número de veces que aparece la cadena para cada esquema:

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;