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

Improve this question
6
  • stackoverflow.com/questions/205736/… Commented Mar 23, 2021 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.. Commented Mar 23, 2021 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'); Commented Mar 23, 2021 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. Commented Mar 23, 2021 at 17:03
  • @mathguy - ah, on second reading I see that. Commented Mar 23, 2021 at 20:30

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.