0

I need a way to search my entire Oracle database for a column that contains the value 'Beef'. What I need is the column name and table name so I can complete my query. Beef is an animal feed type and it is a known value in my database. I just don't know where.....

Essentially, we have a very old very clunky application that I am using SQL data sets generated from Toad freeware to get around. The application shows us laboratory testing information for our companies. The catch is you can only look at one company's lab report at a time, and as a I said, it takes FOREVER. We have over 700 companies we regulate so this is not an option (oh and you can't copy any of the fields).

I have already generated a query that gets me 99% of the information I need until I realized I was missing one column value that for some unearthly reason isn't included with the other attributes of the lab samples. We have around 100 or so tables and many of them aren't even in use. It's a poorly organized database and I've tried manually going through it and simply cannot find the stupid column and I have no idea what it could be named (naming conventions here seem not to apply).

A monkey wrench is: although I've done a decent amount of SQL coding for my job I'm not in IT. My job hooked me up with a read-only access of our database so I could run reports for them etc, but I'm not in the IT section so I don't get write privileges. So a lot of solutions I see that use DDL aren't available to me.

I guess it might be relevant, it looks like we're running oraClient10g.

TOAD screenshot

I've tried this code that I got from here: community.Oracle

but as you can see I don't get any results.

I also tried the one suggested here at stackOverflow, but got a litany of errors so I abandoned that pretty quick. (I figured it's because of my read-only privileges and or my version of Oracle).

Any help would be greatly appreciated.

Community
  • 1
  • 1
Abe Mauricio
  • 1
  • 4
  • 1
    Are you sure the search term is 'Beef'? Case is significant. Have you tried ' WHERE UPPER(' || k.column_name||') LIKE '''%'||:vsearchstr||'%''' – DCookie Jul 15 '16 at 23:26
  • 1
    In addition to DCookie's suggestion, try setting ncount := 0; at the beginning of the LOOP. That may help avoiding problems with NULL, since most of the tables in the database may not return rows for this query. – ramana_k Jul 16 '16 at 01:29
  • Thanks for the comments guys. I think it's 'Beef' because the output from the application has it listed as 'Beef' and not 'BEEF'. But i'll still give your suggestion a try – Abe Mauricio Jul 16 '16 at 03:50
  • The app could be formatting the output with something like INITCAP. – DCookie Jul 16 '16 at 16:58
  • Your subject says "search entire database" but your code will only check the current schema. Are you connecting as the application table owner? If not, you'll need at least ALL_TAB_COLUMNS (and filter on the owner column...) – Patrick Marchand Jul 17 '16 at 01:54
  • OK. I'm not familiar with loops in SQL so this code is a little over my head. Where would I put the ALL_TAB_COLUMNS if I were to include it in the code above? – Abe Mauricio Jul 17 '16 at 20:22
  • I got around my issue by using this code: select owner, table_name, column_name from all_tab_columns where column_name LIKE 'LAB%' and guessing that another attribute - what laboratory it was tested in would have a reliable column name. Just to show you why I would have never found it, the table name was SUSERFLDS, and the column that contains the 'Beef' feed description is PROD_GRAD_PRODTYPE.....yeah, i had no chance. Although, I'm still curious how I could have gotten the code to search the entire database to work – Abe Mauricio Jul 18 '16 at 13:36

0 Answers0