How to Find Specific Values in a Table in Oracle

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 has CUST_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 value 100 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



Leave a reply



Submit