0

I have the following query that gives me a result set of all tables and columns in my Oracle database of VARCHAR columns:

SELECT ATC.OWNER, ATC.TABLE_NAME, ATC.COLUMN_NAME
FROM all_tab_columns ATC
WHERE DATA_TYPE LIKE '%VARCHAR%'

To this I want to add a 4th column that displays the value of ATC.COLUMN_NAME. Is there an easy way of doing this?

I thought of doing a join to a SQL statement that loops through ATC.COLUMN_NAME and outputting the value. The join would be done on the table name.

I don't know if I'm complicating it and I can't think of the SQL. I've tried declaring the above statement in a variable and then using a CTE to interrogate it but I would still need to loop through the table_name and column_name values.

Is there a simpler way?

Edit: Sample data

enter image description here

SSingh
  • 153
  • 3
  • 13
  • Say you have a table with a varchar2 column and 100 rows in the table. How mony output rows do you expect? 100 rows with the values in the table and the first 3 columns always with the same 3 values? – Aleksej Jun 22 '18 at 10:25
  • My query returns a unique column name for each table_name. So although the table_name will appear multiple times, the column_name and table_name row together will be unique – SSingh Jun 22 '18 at 10:28
  • Can you please post some sample data and desired result to clarify your need? – Aleksej Jun 22 '18 at 10:30
  • @SSingh - The table name and column name can be unique, but what about the data in it ? how do you expect to see the data – Sudipta Mondal Jun 22 '18 at 10:30
  • OWNER TABLE COLUMN Value Owner Table 1 REMARKS blah, blah Owner Table 1 RETURN_REMARKS yes Owner Table2 RETURN_REMARKS No answer Owner Table2 REMARKS Tried calling Owner Table 3 RETURN_REMARKS Was in Owner Table 3 MEETING_NOTES Good meeting – SSingh Jun 22 '18 at 10:33
  • 1
    Please add that to your question as formatted text, not as a comment (as it's basically unreadable...) You would have to do this dynamically, but it seems like an odd thing to want to do - kind of a pseudo-export. What do you plan to do with the results? – Alex Poole Jun 22 '18 at 10:33
  • 1
    You need to use dynamic SQL. There are lots of examples on this site of how to do that. – APC Jun 22 '18 at 10:34
  • Alex - The database is large,the query above produces 14k rows approx. Sample data in the post now. I want to scan free text fields for certain patterns of data such as NI numbers for example to ensure GDPR compliance. While we have dedicated fields for such things unfortunatley users have also been referring to individuals in comments and notes fields. I have a REGEX query that correctly identifies the pattern but for ease I wanted that output next to COLUMN_NAME – SSingh Jun 22 '18 at 10:49
  • @SSingh - searching is bit different to just listing... [this shows how to search for a fixed value](https://stackoverflow.com/q/208493/266304), but that or APC's answer could be adapted to use a regular expression filter. – Alex Poole Jun 22 '18 at 11:25

1 Answers1

2

You need to use dynamic SQL. this is a proof of concept, it will not scale well when run against a large database. 

declare
    stmt varchar2(32767);
    val varchar2(4000);
    rc sys_refcursor;
begin
    for r in ( SELECT ATC.OWNER, ATC.TABLE_NAME, ATC.COLUMN_NAME
               FROM all_tab_columns ATC
                WHERE DATA_TYPE LIKE '%VARCHAR%' )
    loop
        stmt := ' select distinct '|| r.column_name || 
                ' from '|| r.owner||'.'||r.table_name;
        open rc for stmt;
        loop
            fetch rc in val;
            exit when rc%notfound;
            dbms_output.put_line ( r.owner||'.'||r.table_name ||'.'|| r.column_name
                   ||': '|| val );
        end loop;
    end loop;
end;
APC
  • 144,005
  • 19
  • 170
  • 281