Oracle - Searching for strings in one table that are contained in another table

Question

I'm working on an Oracle database that has every table exposed in a view. I have made a view name contain the name of the table it is exposing, for clarity reasons. Now I would like to find out if there are tables whose name is not contained in any view, meaning that I forgot to have a view exposing them.

If I query all_tables and all_views, let's assume that I get the following result:

SELECT table_name FROM all_tables:

TABLE1
TABLE2
TABLE3


SELECT view_name FROM all_tables:

TABLE1_VIEW
TABLE2_VIEW

I now would like to query the two results and obtain all the table names that are included in any view name and those that are not. In my example, TABLE1 and TABLE2 are included, but TABLE3 is not.

I have tried looping all the table names and looking for each one of them on the view names result set. I was wondering if there is a more direct approach.

I wouldn't rely on view's name unless you are absolutely sure that all view names have predefined form such as TABLE1_VIEW. Rather check if ALL_VIEWS.TEXT column contains some table name. — Yaroslav Shabalin
– Yaroslav Shabalin, Commented Dec 18, 2013 at 11:49
You are right, mine is not a reliable approach at all. I'm mostly querying this as a challenge, because I'm new to Oracle and I'm not going to do anything automated out of this. — ederbf
– ederbf, Commented Dec 18, 2013 at 11:55

Nick Krasnov · Accepted Answer · 2013-12-19 05:27:40Z

2

Now I would like to find out if there are tables whose name is not contained in any view

To find such tables we can get all table names we are interested in by querying [dba|all|user]_tables data dictionary view and minus those table names that are already part of a view by querying [dba | all | user ]_dependencies data dictionary view:

/* A couple of test tables */ 
SQL> create table t1(
  2    col number
  3  )
  4  ;
Table created

SQL> create table t2(
  2    col number
  3  )
  4  ;
Table created 

/* a view */

SQL> create or replace view v_1 as 
  2     select * 
  3       from t1;

And here are tables that are not included in any view in current schema. In this example user_* data dictionary views have been used, but you are free to use dba_* or all_* or user_* data dictionary view.

SQL> column table_name format a10

SQL> select q.table_name
  2    from user_tables q
  3  minus
  4  select t.referenced_name
  5    from user_dependencies t
  6    where type = 'VIEW'
  7      and t.referenced_type = 'TABLE'
  8  ;

Result:

TABLE_NAME
----------
T2

edited Dec 19, 2013 at 5:27

answered Dec 18, 2013 at 11:57

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

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

1 Comment

ederbf Over a year ago

This approach won't return tables referenced by other views that may exist in the system. But in similar scenarios it may be useful to use the user_dependencies table. Upvote for that.

Incognito · Accepted Answer · 2013-12-18 12:02:03Z

1

Ok, I am not sure how clean this solution is going to be, but it's a quick-fix dirty approach at the least.

Assumption is that, you have created views by appending a string at the end of the table name (which is unaltered)

This will give you all tables, based on which a view is present

SELECT table_name
FROM  user_tables a
WHERE EXISTS (SELECT 1
              FROM  user_views b
              WHERE REGEXP_LIKE(b.view_name, a.table_name));

To get the tables, for which no views are present, just use MINUS

SELECT table_name
FROM user_tables
MINUS
SELECT table_name
FROM  user_tables a
WHERE EXISTS (SELECT 1
              FROM  user_views b
              WHERE REGEXP_LIKE(b.view_name, a.table_name));

answered Dec 18, 2013 at 12:02

Incognito

3,1043 gold badges30 silver badges42 bronze badges

1 Comment

ederbf Over a year ago

This is what I was looking for. I was able to use your code with minimal modifications.

valex · Accepted Answer · 2013-12-18 12:01:56Z

0

If I've got it right:

Tables included in views:

SELECT table_name FROM user_tables T
LEFT JOIN USER_VIEWS V
   ON T.table_name||'_VIEW'=V.view_name
WHERE V.View_name IS NULL

And tables aren't included in views:

SELECT table_name FROM user_tables as T
JOIN USER_VIEWS as V
   ON T.table_name||'_VIEW'=V.view_name

answered Dec 18, 2013 at 12:01

valex

24.2k7 gold badges47 silver badges62 bronze badges

Collectives™ on Stack Overflow

Oracle - Searching for strings in one table that are contained in another table

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related