PL/SQL find me right table and column based on a value :)
Tags: oracle, oraclecode, oracleplsql
Published: 2018-03-23 09:38:42

---
title: PL/SQL find me right table and column based on a value :)
published: true
description: 
tags: Oracle, Oraclecode, OraclePLSQL
---

## Motive

How many times did I write someone an email, and ask them where is a specific value stored in a database (table, column) and the answer came in best scenario tomorrow. So I came with the idea to write my PL/SQL block to find me where is that particular value stored.

## Solution

I wrote simple PL/SQL anonymous block to find me the necessary table and column for my task. 


```sql
SET SERVEROUTPUT ON;
DECLARE
  match_count INTEGER;

--the owner/schema of the tables you are looking at
  v_owner VARCHAR2( 255) :='CUSTOMER' ;

-- data type you look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2( 255) :='VARCHAR2' ;

--The value you are looking for with like "%" operator
  v_search_string VARCHAR2(4000) := '%TGP%' ;

BEGIN
  FOR t IN (SELECT atc.table_name
                  ,atc.column_name
                  ,atc.owner 
            FROM all_tab_cols atc
           WHERE atc.owner = v_owner
             AND data_type =  v_data_type
             -- esclude vir. columns
             AND atc.column_id is not null
             -- exclude views
             AND not exists (select 1 
                               from all_views 
                               where view_name = atc.table_name) ) LOOP
   
    EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' 
         || t.owner || '.'  ||t. table_name|| 
        ' WHERE UPPER("'||t.column_name ||'") LIKE  UPPER(:1)'
    INTO match_count
    USING v_search_string ;

    IF match_count > 0 THEN
      dbms_output.put_line( t. table_name ||' ' ||t.column_name ||' '||match_count );
    END IF;

  END LOOP;
END;
/

```

### parameters

The code has 3 variables:
* v_owner - the schema where query will look for
* v_data_type - data type
* v_search_string - The value you are looking for with like "%" operator

## query
Code executes FOR LOOP on a query to find table_name, column_name and table owner(schema).
The query uses upper parameters **v_owner** and **v_data_type**, also exclude virtual columns and views.


```sql
SELECT atc.table_name
                  ,atc.column_name
                  ,atc.owner 
            FROM all_tab_cols atc
           WHERE atc.owner = v_owner
             AND data_type =  v_data_type
             -- exclude vir. columns
             AND atc.column_id is not null
             -- exclude views
             AND not exists (select 1 
                               from all_views 
                               where view_name = atc.table_name)
```

## EXECUTE IMMEDIATE

```sql
 EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' 
         || t.owner || '.'  ||t. table_name|| 
        ' WHERE UPPER("'||t.column_name ||'") LIKE  UPPER(:1)'
    INTO match_count
    USING v_search_string ;
```

Inside LOOP the code runs EXECUTE IMMEDIATE statement with concatenating table owner, table name, and column and parameter **v_search_string**. So for every column inside every table, it runs to check if there is close enough value to that I look for.

## Output
If the is a match (> 1) output table_name, column_name  and match count to dbms_output. 
```sql
    IF match_count > 0 THEN
      dbms_output.put_line( t. table_name ||' ' ||t.column_name ||' '||match_count );
    END IF;
```

Also since dbms_output has to be turned on and has a limit, I set at the beginning of PL/SQL block
 
```sql
SET SERVEROUTPUT ON;
```
which means the ARGUMENT/VALUES that I will be passing inside dbms_output.put_line prints the argument on the main console (**Script output**).