Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Find Columns that have a particular Value

Hello All, is there a way to scan a particular Schema in an Oracle DB and try to find a particular 'Value' in a Column Name?
Background: Customers needed a new field that is not available in our Warehouse layer. When i spoke to the folks from our Front End Team, they pointed out to a particular 'Value' in their XML that i needed.
I know that our ETL process parses through the XML and populates data across multiple tables in our PSTG schema.
So i would like to scan that PSTG schema / all the tables(columns) in that schema for this particular value.
In the past i have used a query like below , but this just gives me Column names in a particular schema.(Doesnt look at the values in the columns).
-----------------
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') TODAY, T1.OWNER OWNER,
T1.TABLE_NAME "TABLE NAME", T1.COLUMN_NAME "COLUMN NAME",
DECODE(NULLABLE, 'N', 'NOT NULL') "NULL", DATA_TYPE||'('||DATA_LENGTH||')' "DATA TYPE",
LAST_ANALYZED, USER_STATS
FROM SYS.ALL_TAB_COLUMNS T1
WHERE T1.COLUMN_NAME LIKE '%COLUMN_NAME%'
and T1.OWNER LIKE '%SCHEMA_NAME_GOES_HERE%'
ORDER BY T1.TABLE_NAME;
------------------
Any thoughts please ? Thanks a lot!
My oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Best Answers
-
2 steps
Generate your select statements
SELECT 'SELECT ''' || T1.OWNER || '.' || T1.TABLE_NAME || '.' || T1.COLUMN_NAME || ':'', ' || T1.COLUMN_NAME || ' FROM ' || T1.OWNER || '.' || T1.TABLE_NAME || ' WHERE ' || T1.COLUMN_NAME || ' = ''' || '%YOUR_COLUMN_VALUE%' || ''';'
FROM SYS.ALL_TAB_COLUMNS T1
WHERE T1.COLUMN_NAME LIKE '%COLUMN_NAME%'
and T1.OWNER LIKE '%SCHEMA_NAME_GOES_HERE%'
ORDER BY T1.OWNER, T1.COLUMN_NAME ;
and then execute that statements.
If your column is a number or a date you must adjust the select statement to get the correct one.
-
I like my dynamic SQL served as a DBMS_SQL dish.
SQL> create or replace type TStrings is table of varchar2(4000); 2 / Type created. SQL> SQL> <<plBlock>> 2 declare 3 -- table to process 4 TABLE_NAME constant varchar2(30) := 'EMP'; 5 6 -- string filter to search for 7 STRING_FILTER constant varchar2(10) := '%E%'; 8 9 -- for making pretty dynamic SQL 10 TAB constant varchar2(1) := chr(9); 11 LF constant varchar2(1) := chr(10); 12 13 c integer; -- dbms sql cursor handle 14 cur sys_refcursor; 15 rc integer; -- dbms sql return code 16 17 colsList TStrings; -- string cols to search 18 buffer TStrings; -- string cols matches found 19 rid varchar2(50); -- rowid of row with matching col(s) 20 21 -- dynamic SQL 22 sqlProjection varchar2(32767); 23 sqlPredicate varchar2(32767); 24 dynamicSQL varchar2(32767); 25 begin 26 -- get the string columns in the table 27 select 28 column_name bulk collect into colsList 29 from user_tab_cols 30 where table_name = plBlock.TABLE_NAME 31 and data_type like '%CHAR%' 32 order by column_id; 33 34 -- if no string colums then skip table 35 if colsList.count = 0 then 36 return; 37 end if; 38 39 -- build dynamic SQL using colums on table 40 for i in 1 .. colsList.count loop 41 if i = colsList.count then 42 sqlProjection := sqlProjection||TAB||colsList(i); 43 else 44 sqlProjection := sqlProjection||TAB||colsList(i)||','||LF; 45 end if; 46 47 if i = 1 then 48 sqlPredicate := 'from'||TAB||TABLE_NAME||LF; 49 sqlPredicate := sqlPredicate||'where'||TAB||colsList(i)||' like :Filter'||LF; 50 else 51 sqlPredicate := sqlPredicate||'or'||TAB||colsList(i)||' like :Filter'||LF; 52 end if; 53 end loop; 54 dynamicSQL := 'select rowid as RID, TStrings('||LF||sqlProjection||LF||') as COL'||LF||sqlPredicate; 55 dbms_output.put_line( LF||'Dynamic SQL:'||LF||dynamicSQL ); 56 57 -- we need to parse and bind dynamically 58 c := DBMS_SQL.Open_Cursor(); 59 DBMS_SQL.Parse( c, dynamicSQL, dbms_sql.NATIVE ); 60 DBMS_SQL.Bind_Variable( c, 'Filter', STRING_FILTER ); 61 rc := DBMS_SQL.execute( c ); 62 63 -- convert to a refcursor - we can use a static coded fetch as we know 64 -- the SQL projection returned is a rowid and column of type TStrings 65 cur := DBMS_SQL.to_RefCursor( c ); 66 67 dbms_output.put_line ( 'Filter: <column> like '''||STRING_FILTER||'''' ); 68 loop 69 fetch cur into rid, buffer; 70 exit when cur%NotFound; 71 72 -- determine which columns matches 73 dbms_output.put( 'Table='||TABLE_NAME||' | rowid='||rid ); 74 for i in 1 .. buffer.count loop 75 if buffer(i) like STRING_FILTER then 76 dbms_output.put( ' | matched='||colsList(i)||' value='||buffer(i) ); 77 end if; 78 end loop; 79 dbms_output.put_line( '' ); 80 81 end loop; 82 close cur; 83 end; 84 / Dynamic SQL: select rowid as RID, TStrings( ENAME, JOB ) as COL from EMP where ENAME like :Filter or JOB like :Filter Filter: <column> like '%E%' Table=EMP | rowid=AAAUR+AABAAAR4ZAAA | matched=JOB value=CLERK Table=EMP | rowid=AAAUR+AABAAAR4ZAAB | matched=ENAME value=ALLEN | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAC | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAD | matched=ENAME value=JONES | matched=JOB value=MANAGER Table=EMP | rowid=AAAUR+AABAAAR4ZAAE | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAF | matched=ENAME value=BLAKE | matched=JOB value=MANAGER Table=EMP | rowid=AAAUR+AABAAAR4ZAAG | matched=JOB value=MANAGER Table=EMP | rowid=AAAUR+AABAAAR4ZAAI | matched=JOB value=PRESIDENT Table=EMP | rowid=AAAUR+AABAAAR4ZAAJ | matched=ENAME value=TURNER | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAK | matched=JOB value=CLERK Table=EMP | rowid=AAAUR+AABAAAR4ZAAL | matched=ENAME value=JAMES | matched=JOB value=CLERK Table=EMP | rowid=AAAUR+AABAAAR4ZAAN | matched=ENAME value=MILLER | matched=JOB value=CLERK PL/SQL procedure successfully completed. SQL>
This can be implemented as a pipeline table function, with parameters to specify the schema, tables and value to search for.
-
The pipeline version:
SQL> select * from table(SearchTables(filter=>'SALE%')); OWNER TABLE_NAME COLUMN_NAM ROW_ID VALUE ---------- ---------- ---------- -------------------- ---------- BILLYV DEPT DNAME AAAUR8AABAAAPvRAAC SALES BILLYV EMP JOB AAAUR+AABAAAR4ZAAB SALESMAN BILLYV EMP JOB AAAUR+AABAAAR4ZAAC SALESMAN BILLYV EMP JOB AAAUR+AABAAAR4ZAAE SALESMAN BILLYV EMP JOB AAAUR+AABAAAR4ZAAJ SALESMAN 5 rows selected. SQL> select * from table(SearchTables(filter=>'AL%')); OWNER TABLE_NAME COLUMN_NAM ROW_ID VALUE ---------- ---------- ---------- -------------------- ---------- BILLYV EMP ENAME AAAUR+AABAAAR4ZAAB ALLEN 1 row selected.
The code:
create or replace type TStrings is table of varchar2(4000); / create or replace type TRowMatch is object( owner varchar2(128), table_name varchar2(128), column_name varchar2(128), row_id varchar2(100), value varchar2(4000) ); / create or replace type TRowMatchTable is table of TRowMatch; / create or replace function SearchTables( owner varchar2 default user(), tableName varchar2 default '%', filter varchar2 ) return TRowMatchTable pipelined is -- for making pretty dynamic SQL TAB constant varchar2(1) := chr(9); LF constant varchar2(1) := chr(10); c integer; -- dbms sql cursor handle cur sys_refcursor; rc integer; -- dbms sql return code colsList TStrings; -- string cols to search buffer TStrings; -- string cols matches found rid varchar2(50); -- rowid of row with matching col(s) -- dynamic SQL sqlProjection varchar2(32767); sqlPredicate varchar2(32767); dynamicSQL varchar2(32767); begin for tabList in( select owner, table_name from all_tables where owner like SearchTables.owner and table_name like SearchTables.tableName order by 1,2 ) loop -- get the string columns in the table select column_name bulk collect into colsList from all_tab_cols where owner = tabList.owner and table_name = tabList.table_name and data_type like '%CHAR%' order by column_id; -- if no string colums then skip table if colsList.count = 0 then exit; end if; -- build dynamic SQL using colums on table sqlProjection := null; sqlPredicate := null; for i in 1 .. colsList.count loop if i = colsList.count then sqlProjection := sqlProjection||TAB||colsList(i); else sqlProjection := sqlProjection||TAB||colsList(i)||','||LF; end if; if i = 1 then sqlPredicate := 'from'||TAB||tabList.table_name||LF; sqlPredicate := sqlPredicate||'where'||TAB||colsList(i)||' like :Filter'||LF; else sqlPredicate := sqlPredicate||'or'||TAB||colsList(i)||' like :Filter'||LF; end if; end loop; dynamicSQL := 'select rowid as RID, TStrings('||LF||sqlProjection||LF||') as COL'||LF||sqlPredicate; --' -- dbms_output.put_line( LF||'Dynamic SQL:'||LF||dynamicSQL ); -- we need to parse and bind dynamically c := DBMS_SQL.Open_Cursor(); DBMS_SQL.Parse( c, dynamicSQL, DBMS_SQL.NATIVE ); DBMS_SQL.Bind_Variable( c, 'Filter', filter ); rc := DBMS_SQL.execute( c ); -- convert to a refcursor - we can use a static coded fetch as we know -- the SQL projection returned is a rowid and column of type TStrings cur := DBMS_SQL.to_RefCursor( c ); -- dbms_output.put_line ( 'Filter: <column> like '''||STRING_FILTER||'''' ); loop fetch cur into rid, buffer; exit when cur%NotFound; -- determine which columns matches for i in 1 .. buffer.count loop if buffer(i) like filter then pipe row( TRowMatch( tablist.owner, tabList.table_name, colsList(i), rid, buffer(i) ) ); end if; end loop; end loop; close cur; end loop; return; end; /
Answers
-
2 steps
Generate your select statements
SELECT 'SELECT ''' || T1.OWNER || '.' || T1.TABLE_NAME || '.' || T1.COLUMN_NAME || ':'', ' || T1.COLUMN_NAME || ' FROM ' || T1.OWNER || '.' || T1.TABLE_NAME || ' WHERE ' || T1.COLUMN_NAME || ' = ''' || '%YOUR_COLUMN_VALUE%' || ''';'
FROM SYS.ALL_TAB_COLUMNS T1
WHERE T1.COLUMN_NAME LIKE '%COLUMN_NAME%'
and T1.OWNER LIKE '%SCHEMA_NAME_GOES_HERE%'
ORDER BY T1.OWNER, T1.COLUMN_NAME ;
and then execute that statements.
If your column is a number or a date you must adjust the select statement to get the correct one.
-
I show exactly what you want as the final example in a post I made about using DBMS_XMLGen for dynamic SQL, that is, searching all columns for a specific value.
-
I like my dynamic SQL served as a DBMS_SQL dish.
SQL> create or replace type TStrings is table of varchar2(4000); 2 / Type created. SQL> SQL> <<plBlock>> 2 declare 3 -- table to process 4 TABLE_NAME constant varchar2(30) := 'EMP'; 5 6 -- string filter to search for 7 STRING_FILTER constant varchar2(10) := '%E%'; 8 9 -- for making pretty dynamic SQL 10 TAB constant varchar2(1) := chr(9); 11 LF constant varchar2(1) := chr(10); 12 13 c integer; -- dbms sql cursor handle 14 cur sys_refcursor; 15 rc integer; -- dbms sql return code 16 17 colsList TStrings; -- string cols to search 18 buffer TStrings; -- string cols matches found 19 rid varchar2(50); -- rowid of row with matching col(s) 20 21 -- dynamic SQL 22 sqlProjection varchar2(32767); 23 sqlPredicate varchar2(32767); 24 dynamicSQL varchar2(32767); 25 begin 26 -- get the string columns in the table 27 select 28 column_name bulk collect into colsList 29 from user_tab_cols 30 where table_name = plBlock.TABLE_NAME 31 and data_type like '%CHAR%' 32 order by column_id; 33 34 -- if no string colums then skip table 35 if colsList.count = 0 then 36 return; 37 end if; 38 39 -- build dynamic SQL using colums on table 40 for i in 1 .. colsList.count loop 41 if i = colsList.count then 42 sqlProjection := sqlProjection||TAB||colsList(i); 43 else 44 sqlProjection := sqlProjection||TAB||colsList(i)||','||LF; 45 end if; 46 47 if i = 1 then 48 sqlPredicate := 'from'||TAB||TABLE_NAME||LF; 49 sqlPredicate := sqlPredicate||'where'||TAB||colsList(i)||' like :Filter'||LF; 50 else 51 sqlPredicate := sqlPredicate||'or'||TAB||colsList(i)||' like :Filter'||LF; 52 end if; 53 end loop; 54 dynamicSQL := 'select rowid as RID, TStrings('||LF||sqlProjection||LF||') as COL'||LF||sqlPredicate; 55 dbms_output.put_line( LF||'Dynamic SQL:'||LF||dynamicSQL ); 56 57 -- we need to parse and bind dynamically 58 c := DBMS_SQL.Open_Cursor(); 59 DBMS_SQL.Parse( c, dynamicSQL, dbms_sql.NATIVE ); 60 DBMS_SQL.Bind_Variable( c, 'Filter', STRING_FILTER ); 61 rc := DBMS_SQL.execute( c ); 62 63 -- convert to a refcursor - we can use a static coded fetch as we know 64 -- the SQL projection returned is a rowid and column of type TStrings 65 cur := DBMS_SQL.to_RefCursor( c ); 66 67 dbms_output.put_line ( 'Filter: <column> like '''||STRING_FILTER||'''' ); 68 loop 69 fetch cur into rid, buffer; 70 exit when cur%NotFound; 71 72 -- determine which columns matches 73 dbms_output.put( 'Table='||TABLE_NAME||' | rowid='||rid ); 74 for i in 1 .. buffer.count loop 75 if buffer(i) like STRING_FILTER then 76 dbms_output.put( ' | matched='||colsList(i)||' value='||buffer(i) ); 77 end if; 78 end loop; 79 dbms_output.put_line( '' ); 80 81 end loop; 82 close cur; 83 end; 84 / Dynamic SQL: select rowid as RID, TStrings( ENAME, JOB ) as COL from EMP where ENAME like :Filter or JOB like :Filter Filter: <column> like '%E%' Table=EMP | rowid=AAAUR+AABAAAR4ZAAA | matched=JOB value=CLERK Table=EMP | rowid=AAAUR+AABAAAR4ZAAB | matched=ENAME value=ALLEN | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAC | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAD | matched=ENAME value=JONES | matched=JOB value=MANAGER Table=EMP | rowid=AAAUR+AABAAAR4ZAAE | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAF | matched=ENAME value=BLAKE | matched=JOB value=MANAGER Table=EMP | rowid=AAAUR+AABAAAR4ZAAG | matched=JOB value=MANAGER Table=EMP | rowid=AAAUR+AABAAAR4ZAAI | matched=JOB value=PRESIDENT Table=EMP | rowid=AAAUR+AABAAAR4ZAAJ | matched=ENAME value=TURNER | matched=JOB value=SALESMAN Table=EMP | rowid=AAAUR+AABAAAR4ZAAK | matched=JOB value=CLERK Table=EMP | rowid=AAAUR+AABAAAR4ZAAL | matched=ENAME value=JAMES | matched=JOB value=CLERK Table=EMP | rowid=AAAUR+AABAAAR4ZAAN | matched=ENAME value=MILLER | matched=JOB value=CLERK PL/SQL procedure successfully completed. SQL>
This can be implemented as a pipeline table function, with parameters to specify the schema, tables and value to search for.
-
The pipeline version:
SQL> select * from table(SearchTables(filter=>'SALE%')); OWNER TABLE_NAME COLUMN_NAM ROW_ID VALUE ---------- ---------- ---------- -------------------- ---------- BILLYV DEPT DNAME AAAUR8AABAAAPvRAAC SALES BILLYV EMP JOB AAAUR+AABAAAR4ZAAB SALESMAN BILLYV EMP JOB AAAUR+AABAAAR4ZAAC SALESMAN BILLYV EMP JOB AAAUR+AABAAAR4ZAAE SALESMAN BILLYV EMP JOB AAAUR+AABAAAR4ZAAJ SALESMAN 5 rows selected. SQL> select * from table(SearchTables(filter=>'AL%')); OWNER TABLE_NAME COLUMN_NAM ROW_ID VALUE ---------- ---------- ---------- -------------------- ---------- BILLYV EMP ENAME AAAUR+AABAAAR4ZAAB ALLEN 1 row selected.
The code:
create or replace type TStrings is table of varchar2(4000); / create or replace type TRowMatch is object( owner varchar2(128), table_name varchar2(128), column_name varchar2(128), row_id varchar2(100), value varchar2(4000) ); / create or replace type TRowMatchTable is table of TRowMatch; / create or replace function SearchTables( owner varchar2 default user(), tableName varchar2 default '%', filter varchar2 ) return TRowMatchTable pipelined is -- for making pretty dynamic SQL TAB constant varchar2(1) := chr(9); LF constant varchar2(1) := chr(10); c integer; -- dbms sql cursor handle cur sys_refcursor; rc integer; -- dbms sql return code colsList TStrings; -- string cols to search buffer TStrings; -- string cols matches found rid varchar2(50); -- rowid of row with matching col(s) -- dynamic SQL sqlProjection varchar2(32767); sqlPredicate varchar2(32767); dynamicSQL varchar2(32767); begin for tabList in( select owner, table_name from all_tables where owner like SearchTables.owner and table_name like SearchTables.tableName order by 1,2 ) loop -- get the string columns in the table select column_name bulk collect into colsList from all_tab_cols where owner = tabList.owner and table_name = tabList.table_name and data_type like '%CHAR%' order by column_id; -- if no string colums then skip table if colsList.count = 0 then exit; end if; -- build dynamic SQL using colums on table sqlProjection := null; sqlPredicate := null; for i in 1 .. colsList.count loop if i = colsList.count then sqlProjection := sqlProjection||TAB||colsList(i); else sqlProjection := sqlProjection||TAB||colsList(i)||','||LF; end if; if i = 1 then sqlPredicate := 'from'||TAB||tabList.table_name||LF; sqlPredicate := sqlPredicate||'where'||TAB||colsList(i)||' like :Filter'||LF; else sqlPredicate := sqlPredicate||'or'||TAB||colsList(i)||' like :Filter'||LF; end if; end loop; dynamicSQL := 'select rowid as RID, TStrings('||LF||sqlProjection||LF||') as COL'||LF||sqlPredicate; --' -- dbms_output.put_line( LF||'Dynamic SQL:'||LF||dynamicSQL ); -- we need to parse and bind dynamically c := DBMS_SQL.Open_Cursor(); DBMS_SQL.Parse( c, dynamicSQL, DBMS_SQL.NATIVE ); DBMS_SQL.Bind_Variable( c, 'Filter', filter ); rc := DBMS_SQL.execute( c ); -- convert to a refcursor - we can use a static coded fetch as we know -- the SQL projection returned is a rowid and column of type TStrings cur := DBMS_SQL.to_RefCursor( c ); -- dbms_output.put_line ( 'Filter: <column> like '''||STRING_FILTER||'''' ); loop fetch cur into rid, buffer; exit when cur%NotFound; -- determine which columns matches for i in 1 .. buffer.count loop if buffer(i) like filter then pipe row( TRowMatch( tablist.owner, tabList.table_name, colsList(i), rid, buffer(i) ) ); end if; end loop; end loop; close cur; end loop; return; end; /