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.
search value in the entire table oracle sql
If all of your columns have compatible data types and you just want to return any rows that have at least one column that matches your value here is one way to accomplish that:
SELECT *
FROM test_d
WHERE 'ARFTU' IN (col1, col2, ... coln);
check
Find a column and a value from a schema scan all the table in oracle
You may write the output to a file if you get buffer overflow on set Serveroutput
otherwise this should do.Output will have all tables that has 'ABC'
column and respective count shows count of record with ABC
column value as 1234
.
SET SERVEROUTPUT ON 100000
DECLARE
lv_count number(10):=0;
l_str varchar2 (1000);
BEGIN
FOR V1 IN
(select distinct table_name
from dba_tab_columns
where column_name = 'ABC')
LOOP
BEGIN
lv_query := ' select count(*) from '||v1.table_name||' where ABC =1234';
EXECUTE IMMEDIATE lv_query INTO lv_count;
dbms_output.put_line(v1.table_name||' --> '||lv_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('OTHERS EXCEPTION '||v1.table_name||' ERRCODE '||SQLERRM||' '||SUBSTR(SQLCODE,1,200));
END;
END LOOP;
END;
To find all tables having column_name ABC, simple query as below should do.
select table_name
from dba_tab_columns
where column_name = UPPER('ABC');
PS: Metadata tables(dba_Tab_columns) stores column_name in upper case, to avoid any issues with case ,converting the case to upper for the literal.
Second query in PL/SQL block,
SET SERVEROUTPUT ON 100000
DECLARE
lv_count number(10):=0;
l_str varchar2 (1000);
lv_col_name varchar2(255) :='ABC';
BEGIN
FOR V1 IN
(select distinct table_name
from dba_tab_columns
where column_name = lv_col_name)
LOOP
dbms_output.put_line(lv_col_name||' '||v1.table_name);
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
ACCOUNT
which hasCUST_ID
column 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_ID
column with value100
in 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_USERS
table 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 Get a List of Column Names on Sqlite3 Database
Make SQL Select Same Row Multiple Times
How to Convert from Blob to Text in MySQL
How to Close Idle Connections in Postgresql Automatically
Postgresql How to Insert a Value With Double Quotes Inside Double Quotes
Select Multiple Columns Count One Column and Group by One Column in One Table
Select Query to Remove Non-Numeric Characters
Subtraction Between Two SQL Queries
How to Replace Single-Quote With Double-Quote in SQL Query - Oracle 10G
How to Compare Timestamp Dates With Date-Only Parameter in MySQL
How to Subtract One Month from a Date Column
Postgresql Error: Relation Already Exists
How to Select the Last Record from MySQL Table Using SQL Syntax
The Network Adapter Could Not Establish the Connection in SQL Developer
Way to Find Data of a SQL Table With Same Status for Consecutive 3 Days
Loop Through Table Rows and Call Stored Procedure on Every Row
How to Use Json_Extract in MySQL and Get a String Without the Quotes