CODEWITHSUNDEEP
sqloracle-database
ederbf
ederbf

Reputation: 1713

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

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.

Upvotes: 1

Views: 817

Answers (3)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 2

Incognito
Incognito

Reputation: 3084

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));

Upvotes: 1

valex
valex

Reputation: 24134

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

Upvotes: 0

Related Questions