0

How to find the table name based on a value I pass in Oracle ? The query should check all the tables in the schema to check if the given value exists in any of the tables

Osceria
  • 345
  • 2
  • 14
  • https://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle – Raseena Abdul Mar 23 '21 at 15:01
  • Why would you ever need to perform such a task? Are you hunting for malware? I have a hard time understanding a legitimate business need for doing what you are asking.. –  Mar 23 '21 at 15:43
  • I tend to agree with @Mathguy. Finding the existence of a table is a simple as 'SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME = 'NAMEOFMYTABLE'; Why would you need a procedure for this, as implied by " based on a value I pass"? Oh, and to account for possibility of MixedCaseNames (anathema in oracle) you would make the WHERE clause read WHERE UPPER(TABLE_NAME) = UPPER('MYTABLENAME'); – EdStevens Mar 23 '21 at 16:40
  • @EdStevens - I think it's even worse than that. The OP wants to find a table that has a given value in a row/column combination in that table. Like "find a table that has a value of `'Mandelbrot'` in some row in one of the columns". I've seen this kind of request several times in the past, from various users. I still don't understand what a legitimate need might be for such a search. Not to mention searching for other data types: searching for a given date, for example - not even knowing if it may be stored as `date`, `timestamp` or even `varchar2`. –  Mar 23 '21 at 17:03
  • @mathguy - ah, on second reading I see that. – EdStevens Mar 23 '21 at 20:30
  • The expectation is to find the table name to help in data extraction. We don't know the table name that holds the value of a field. Hence, attempting to find out in reverse way based on the values the field has on the app. – Osceria Mar 26 '21 at 08:19

1 Answers1

0

Based on your description you basically have three different options with which you can achieve the desired result:

  1. You can query the user_tables table and check all the tables, available on your schema. This table can be called from any user, so you won't require any specific privileges.

    SELECT * FROM USER_TABLES WHERE TABLE_NAME LIKE '%SOMETHING%';

  2. You can query the all_tables table. For this, most likely, you will need some better privileges as you would require when calling user_tables.

    SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%SOMETHING%';

  3. You can query the dba_tables table. As you would expect, for this table you will need great privileges to be able to retrieve the desired information.

    SELECT * FROM DBA_TABLES WHERE TABLE_NAME LIKE '%SOMETHING%';

HINT: If you are not quite sure that you are looking for a table, you can use the ALL_OBJECTS table and identify the type of the object.

Paul C.
  • 167
  • 1
  • 8
  • it seems you and I both mis-read/mis-understood the OP's question. Take a look at @Mathguy response to my comment, then re-read (carefully) the OP's question. – EdStevens Mar 23 '21 at 20:32