0

I am looking to find all of the field names and their associated table names where the field value is "x". My company has switched to a new backend DB and it is necessary for me to do a lot of digging and mapping on my own. I am trying to find specific fields based on their values.

For instance if I were to try to find All Tables with field Name LIKE "X" I would use:

select owner, table_name, column_name from all_tab_columns where column_name LIKE '%X%';

I am trying to find the equivalent query to find all Fields with value 'X". I am looking for something like the below query that does not work. 

SELECT owner, table_name, column_name FROM all_tab_columns WHERE field_name LIKE '%PTREG%';
Chuck0185
  • 531
  • 3
  • 15
  • 36
  • What does "not work?" – OldProgrammer Jan 10 '20 at 03:16
  • *"find the equivalent query to find all Fields with **value** 'X""* Did you really mean **value** X? So you want to list the name of all tables where any `CHAR` or `VARCHAR2` column has at least row with the value `'X'`? --- *Hint:* If so, you need a separate `SELECT` statement for each table, with a custom `WHERE` clause naming all text columns, e.g. `SELECT 'table1' FROM DUAL WHERE EXISTS ( SELECT * FROM table1 WHERE col1 = 'X' OR col4 = 'X' OR col6 = 'X' )`. – Andreas Jan 10 '20 at 03:16
  • Thank you foro the responsne! I was using X as an example for whatever I need to input. For instance I am looking to retrieve any field in the database and the associated table that contains the value (with wildcards) '%PTREG%'. So if there is a field named [PersonType] in a table named [Employees] and ne of the values is "PTREG US", then I need to pull the field name, the table name, and the value ("PTREG US"). Make sense? – Chuck0185 Jan 10 '20 at 03:23
  • See [SQL to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA](https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema/) – Lalit Kumar B Jan 10 '20 at 05:20

0 Answers0