0

I have a piece of data, in this case, Toyota. I have 2 owners, foo & bar. Each owner has about completely different 50+ tables each. All I know is the data, Toyota is in one or more of the 2 owner's tables. The challenge is, I am trying to find where Toyota came from, from the database. How can I select the tables, column name, and owner which contains the data Toyota?

I understand from context Toyota is a car manufacture, so I was doing something like this, but then I have to manually check if that the particular table/column has the data Toyota. Also what if the column name is car_man, then my statement below completely doesn't work.

select table_name, column_name, owner
FROM all_tab_columns
where (owner = 'foo' or owner = 'bar')
and column_name LIKE '%manufacture%';
Coolio
  • 1
  • 1
  • Do you have to do all this in a single SQL query? – Bobby Durrett Apr 29 '21 at 23:23
  • @BobbyDurrett it would be preferred if I could. But not a requirement. The challenge is to find where `Toyota` came from. – Coolio Apr 29 '21 at 23:23
  • Your life would be easier if you have a single table with an "owner" column and a view over it for each owner. – Bohemian Apr 29 '21 at 23:25
  • @Bohemian Not too sure what you mean? Do you mean I would have to run an SQL statement for every owner? Thus, a view would allow me to run 1 statement to look through both users? – Coolio Apr 29 '21 at 23:29
  • You have to query all 100+ tables if you are looking for the string "Toyota" in every column. You have to check every column in every table (at least every character column). Unless you are looking for columns named 'Toyota' which would be different. The referenced post covers this. – Bobby Durrett Apr 29 '21 at 23:32
  • @SayanMalakshinov It doesn't answer my question as it errors out. – Coolio Apr 29 '21 at 23:34
  • You said *each owner has about 50+ tables each*. If they are the same (or could be made to be the same), then you only need 50 tables if you add a column to distinguish the *rows* of that table that apply to each user. You could create a view `create view xxx as select * from one_table where owner_id = some_owner_identifier`. The identifier could be the user or some something else. – Bohemian Apr 29 '21 at 23:35
  • Have you tried `where column_name like '%Toyota%'` ? – Bohemian Apr 29 '21 at 23:36
  • @BobbyDurrett the table name, column name and owner is what I am looking for. The column name is not `Toyota`. I want to find any cell that is exactly `Toyota`. Then I want to know what column that cell is in. What table that column is in and finally who is the owner. – Coolio Apr 29 '21 at 23:36
  • 1
    If you want to search every column of every row in your db I suggest exporting the database to files and using grep. – Bohemian Apr 29 '21 at 23:37

0 Answers0