Buscar en todos los campos de todas las tablas un valor específico (Oracle)
¿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, por lo que sé que la consulta podría llevar mucho tiempo. Pero lo único que sé es que un valor para el campo que me gustaría consultar es 1/22/2008P09RR8. <
Intenté usar esta declaración a continuación para encontrar una columna apropiada según el nombre que creo que debería tener, pero no arrojó resultados.
SELECT * from dba_objects
WHERE object_name like '%DTN%'
No hay absolutamente ninguna documentación sobre esta base de datos y no tengo idea de dónde se extrae este campo.
¿Alguna idea?
Cita:
Intenté usar esta declaración a continuación para encontrar una columna apropiada según el nombre que creo que debería tener, pero no arrojó resultados.*
SELECT * from dba_objects WHERE object_name like '%DTN%'
Una columna no es un objeto. Si quiere decir que espera que el nombre de la 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 sólo una suposición de su parte, probablemente no ayude.
Por cierto, ¿qué tan seguro está de que '22/01/2008P09RR8' es 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 ubicado en un objeto de tabla anidado. Por lo tanto, es posible que esté en una búsqueda inútil al intentar verificar cada columna para ese valor. ¿No puede comenzar con cualquier aplicación cliente que muestre este valor e intentar averiguar qué consulta está utilizando para obtenerlo?
De todos modos, la respuesta de diciu proporciona un método para generar consultas SQL para verificar el valor de cada columna de cada tabla. También puedes hacer cosas similares completamente en una sesión de SQL usando 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;
/
También hay algunas formas de hacerlo más eficiente.
En este caso, dado el valor que buscas, claramente puedes eliminar 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 sea como '%CHAR%'.
En lugar de una consulta por columna, podría crear una consulta por tabla de esta manera:
SELECT * FROM table1
WHERE column1 = 'value'
OR column2 = 'value'
OR column3 = 'value'
...
;
Hice algunas modificaciones al código anterior para que funcione más rápido si buscas con un solo propietario. Sólo tienes que cambiar las 3 variables v_owner, v_data_type y v_search_string para que se ajusten a lo que estás 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;
/
Sé que este es un tema antiguo. Pero veo un comentario a la pregunta que pregunta si se podría hacer en SQLlugar de usar PL/SQL. Así que pensé en publicar una solución.
La siguiente demostración consiste en buscar un VALOR en todas las COLUMNAS de todas las TABLAS en un ESQUEMA completo :
- Buscar un tipo de CARACTER
Busquemos el valor KINGen el SCOTTesquema.
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 NUMÉRICO
Busquemos el valor 20en el SCOTTesquema.
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>
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;
/
Sí, puede y su DBA lo odiará y encontrará que se clave los zapatos en el piso porque eso causará muchas E/S y reducirá mucho 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 ejecución, usando v$sessiony v$sqlarea. Esto cambia según la versión de Oracle. Esto reducirá el espacio y no afectará a todo.
