Search All Fields In All Tables For A Specific Value (Oracle)
Quote:
I've tried using this statement below
to find an appropriate column based on
what I think it should be named but it
returned no results.*SELECT * from dba_objects WHERE
object_name like '%DTN%'
A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:
SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
But if the 'DTN' string is just a guess on your part, that probably won't help.
By the way, how certain are you that '1/22/2008P09RR8' is a value selected directly from a single column? If you don't know at all where it is coming from, it could be a concatenation of several columns, or the result of some function, or a value sitting in a nested table object. So you might be on a wild goose chase trying to check every column for that value. Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it?
Anyway, diciu's answer gives one method of generating SQL queries to check every column of every table for the value. You can also do similar stuff entirely in one SQL session using a PL/SQL block and dynamic SQL. Here's some hastily-written code for that:
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;
/
There are some ways you could make it more efficient too.
In this case, given the value you are looking for, you can clearly eliminate any column that is of NUMBER or DATE type, which would reduce the number of queries. Maybe even restrict it to columns where type is like '%CHAR%'.
Instead of one query per column, you could build one query per table like this:
SELECT * FROM table1
WHERE column1 = 'value'
OR column2 = 'value'
OR column3 = 'value'
...
;
Please tell me how can I find all the values in Oracle database if I don't know the specific schema\table\column?
Well, anything will take a long time if you want to scan all tables and all columns (unless it's a tiny schema as Scott's).
Anyway: you'll need dynamic SQL. Here's an example: I'm searching through all CHAR-like datatype columns in my own schema (line #8), looking for KING (line #15):
SQL> DECLARE
2 l_str VARCHAR2 (500);
3 l_cnt NUMBER := 0;
4 BEGIN
5 FOR cur_r IN (SELECT u.table_name, u.column_name
6 FROM user_tab_columns u JOIN user_tables t
7 ON u.table_name = t.table_name
8 WHERE u.data_type LIKE '%CHAR%')
9 LOOP
10 l_str :=
11 'SELECT COUNT(*) FROM '
12 || cur_r.table_name
13 || ' WHERE '
14 || cur_r.column_name
15 || ' like (''%KING%'')';
16
17 EXECUTE IMMEDIATE (l_str)
18 INTO l_cnt;
19
20 IF l_cnt > 0
21 THEN
22 DBMS_OUTPUT.put_line (l_cnt || ' : ' || cur_r.table_name);
23 END IF;
24 END LOOP;
25 END;
26 /
1 : TABLE22
1 : EMP
PL/SQL procedure successfully completed.
SQL>
The result says that KING appears in two tables (TABLE22 and EMP), once in each of them.
Find tables & columns that will have the specific data in oracle
You:
- Do not consider the owner of the table in the dynamic query.
- May need to quote the identifiers.
- Can also filter on the
data_length.
Like this:
DECLARE
match_count INTEGER;
v_owner VARCHAR2(255) := 'GWEB';
v_data_type VARCHAR2(255) := 'VARCHAR2';
v_search_string VARCHAR2(4000) :='tom@example.com';
BEGIN
FOR t IN (
SELECT table_name,
column_name
FROM all_tab_cols
WHERE owner = v_owner
AND data_type = v_data_type
AND data_length >= LENGTH(v_search_string)
)
LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*)'
|| ' FROM "'||v_owner||'"."'||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;
/
db<>fiddle here
Oracle Search all tables all columns for string
At a minimum, you need to query ALL_TAB_COLUMNS, not ALL_TABLES
DECLARE
match_count integer;
v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN
FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.owner || '.' || 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.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
If you are looking for a string, however, you would almost certainly want to restrict yourself to looking for columns that could store a string. It wouldn't make sense, for example, to search a DATE column for a string. And unless you have a great deal of a priori knowledge about what a BLOB column contains and the ability to parse the BLOB column's binary formatting, it wouldn't make sense to search a BLOB column for a string. Given that, I suspect you want something more like
DECLARE
match_count integer;
v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN
FOR t IN (SELECT owner,
table_name,
column_name
FROM all_tab_columns
WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2',
'CLOB', 'NCLOB') )
LOOP
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.owner || '.' || 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.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
END IF;
EXCEPTION
WHEN others THEN
dbms_output.put_line( 'Error encountered trying to read ' ||
t.column_name || ' from ' ||
t.owner || '.' || t.table_name );
END;
END LOOP;
END;
/
Of course, this is going to be insanely slow-- you'd full scan every table once for every string column in the table. With moderately large tables and a moderate number of string columns, that is likely to take quite a while.
Search value in all tables of database
Thanks for all! Previous script implemented very slowly.
I got my value with this script:
DECLARE
match_count integer;
v_search_string varchar2(4000) := 'advcgtfs000080000ict1mosqiomujrk';
BEGIN
FOR t IN (SELECT owner,
table_name,
column_name
FROM all_tab_columns
WHERE data_type in ('VARCHAR2') )
LOOP
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.owner || '.' || 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.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
END IF;
EXCEPTION
WHEN others THEN
dbms_output.put_line( 'Error encountered trying to read ' ||
t.column_name || ' from ' ||
t.owner || '.' || t.table_name );
END;
END LOOP;
END;
/
Oracle - SQL to get data from all tables with specific column and value
I am not sure what is expected so answering as per my understanding as following:
- Let's say we have a table
ACCOUNTwhich hasCUST_IDcolumn as following:
SQL> SELECT OWNER, A.TABLE_NAME, COLUMN_NAME FROM
2 ALL_TAB_COLS A
3 WHERE
4 A.COLUMN_NAME = 'CUST_ID';
OWNER TABLE_NAME COLUMN_NAME
---------- -------------------- --------------------
TEJASH ACCOUNT CUST_ID
SQL> SELECT * FROM ACCOUNT;
ACC_NR SUM_ CUST_ID
---------- ---------- ----------
500 3400 100
- Now, I want to search in all the tables (on which I have access) to find all the tables having a
CUST_IDcolumn with value100in it.
SQL> SELECT
2 table_name, COLUMN_NAME,
3 to_number(xmlquery('/ROWSET/ROW/C/text()'
4 passing xmltype(dbms_xmlgen.getxml(
5 'select count(1) as c '
6 || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME || '=''100'''))
7 returning content)) as cOUNT
8 FROM ALL_TAB_COLS A
9 WHERE A.COLUMN_NAME = 'CUST_ID';
TABLE_NAME COLUMN_NAME COUNT
-------------------- -------------------- ----------
ACCOUNT CUST_ID 1
Here, every table with the column CUST_ID will appear and COUNT column displays the number of records in that table with CUST_ID = 100
- Now, let's add a column to another table and see the effect:
SQL> ALTER TABLE ACTIVE_USERS ADD CUST_ID VARCHAR2(100);
Table altered.
SQL> INSERT INTO ACTIVE_USERS VALUES (5,SYSDATE, SYSDATE, 200);
1 row created.
SQL> SELECT * FROM ACTIVE_USERS;
CUST_NUM START_DATE END_DATE CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19 21-NOV-19 200
########## 21-NOV-19 21-NOV-19
########## 01-JAN-18 01-JAN-19
########## 01-JAN-18
########## 01-JAN-19 01-JUN-19
########## 01-JAN-17 01-MAR-19
6 rows selected.
- Now, Again running our query to find the data from all the tables:
SQL> SELECT
2 table_name, COLUMN_NAME,
3 to_number(xmlquery('/ROWSET/ROW/C/text()'
4 passing xmltype(dbms_xmlgen.getxml(
5 'select count(1) as c '
6 || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME || '=''100'''))
7 returning content)) as cOUNT
8 FROM ALL_TAB_COLS A
9 WHERE A.COLUMN_NAME = 'CUST_ID';
TABLE_NAME COLUMN_NAME COUNT
-------------------- -------------------- ----------
ACCOUNT CUST_ID 1
ACTIVE_USERS CUST_ID 0
SQL>
And, It worked!!
- Again adding more data to the
ACTIVE_USERStable and see the result.
SQL> INSERT INTO ACTIVE_USERS VALUES (6,SYSDATE-1, SYSDATE, 100);
1 row created.
SQL> INSERT INTO ACTIVE_USERS VALUES (7,SYSDATE-2, SYSDATE, 100);
1 row created.
SQL> INSERT INTO ACTIVE_USERS VALUES (8,SYSDATE-3, SYSDATE, 100);
1 row created.
SQL> SELECT * FROM ACTIVE_USERS;
CUST_NUM START_DATE END_DATE CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19 21-NOV-19 200
########## 20-NOV-19 21-NOV-19 100
########## 19-NOV-19 21-NOV-19 100
########## 18-NOV-19 21-NOV-19 100
########## 21-NOV-19 21-NOV-19
########## 01-JAN-18 01-JAN-19
########## 01-JAN-18
########## 01-JAN-19 01-JUN-19
########## 01-JAN-17 01-MAR-19
9 rows selected.
- Let's check the result of our query now.
SQL> SELECT
2 table_name, COLUMN_NAME,
3 to_number(xmlquery('/ROWSET/ROW/C/text()'
4 passing xmltype(dbms_xmlgen.getxml(
5 'select count(1) as c '
6 || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME || '=''100'''))
7 returning content)) as cOUNT
8 FROM ALL_TAB_COLS A
9 WHERE A.COLUMN_NAME = 'CUST_ID';
TABLE_NAME COLUMN_NAME COUNT
-------------------- -------------------- ----------
ACCOUNT CUST_ID 1
ACTIVE_USERS CUST_ID 3
SQL>
Again, It worked !! :)
Cheers!!
Related Topics
How to Select the First Row of Each Group
Inserting Multiple Rows in a Single SQL Query
How to Declare a Variable in MySQL
Difference Between Single and Double Quotes in Sql
How to Delete from Multiple Tables in MySQL
Get List of All Tables in Oracle
How to Combine Date from One Field With Time from Another Field - Ms SQL Server
In VS or in the SQL Where Clause
What's the Difference Between Truncate and Delete in Sql
Remove Identity from a Column in a Table
Best Way to Do Multi-Row Insert in Oracle
How to Comma Delimit Multiple Rows into One Column
Equivalent of Limit and Offset For SQL Server
Get Top Results For Each Group (In Oracle)
How to See the Raw SQL Queries Django Is Running