Find all the tables and column which has email in Oracle

In my Oracle database I have around 2K tables and want to find the table and column name which has email in it.

Condition: May not be column name would have email tag inside it but the value contained by the column is <something>@<anything>.<tail>.

Took help from previous similar thread, it is running without any output

DECLARE
    l_cmd     VARCHAR2 (2000);
    l_found   INTEGER;
BEGIN
    FOR eachcol IN (  SELECT *
                        FROM all_tab_cols a
                       WHERE a.data_type = 'VARCHAR2'
                         AND owner = 'SEARCHSCHEMANAME'
                    ORDER BY table_name, column_name)
    LOOP
        l_cmd   :=
               'select count(*) c from '
            || eachcol.owner
            || '.'
            || eachcol.table_name
            || ' where '
            || LOWER (eachcol.column_name)
            || q'[ LIKE '%@%.%' AND ROWNUM = 1]';

        EXECUTE IMMEDIATE l_cmd INTO l_found;

        IF l_found > 0
        THEN
            DBMS_OUTPUT.put_line (
                   RPAD (eachcol.owner || '.' || eachcol.table_name || '.' || eachcol.column_name, 92)
                || ' may contain email addresses'
            );
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (l_cmd);
        DBMS_OUTPUT.put_line (SQLERRM);
        RAISE;
END;

Output:

If it doesn't produce any output, then:

• there are no columns that contain such data
• there are no errors either
• you forgot to enable serveroutput

I slightly modified your code so that it

• selects data from user_tab_cols (didn't feel like trying all_tab_cols)
• contains inner BEGIN-EXCEPTION-END block which handles possible exceptions, but lets loop to continue (why would I want to stop execution on error? Nope, I want it to go on)

It kind of works in my schema, so I presume that it should work in yours too (unless you hit one or more of 3 possibilities I mentioned at the beginning).

SQL> SET LINESIZE 200
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2     l_cmd    VARCHAR2 (2000);
3     l_found  INTEGER;
4  BEGIN
5     FOR eachcol IN (  SELECT *
6                         FROM user_tab_cols a
7                        WHERE a.data_type = 'VARCHAR2'
8                     --                         AND owner = 'SEARCHSCHEMANAME'
9                     ORDER BY table_name, column_name)
10     LOOP
11        BEGIN
12           l_cmd :=
13                 'select count(*) c from '
14              --            || eachcol.owner
15              --            || '.'
16              || eachcol.table_name
17              || ' where '
18              || '"' || eachcol.column_name || '"'
19              || q'[ LIKE '%@%.%' AND ROWNUM = 1]';
20
21           EXECUTE IMMEDIATE l_cmd
22              INTO l_found;
23
24           IF l_found > 0
25           THEN
26              DBMS_OUTPUT.put_line (
27                    RPAD (                                         --eachcol.owner || '.' ||
28                          eachcol.table_name || '.' || eachcol.column_name, 92)
29                 || ' may contain email addresses');
30           END IF;
31        EXCEPTION
32           WHEN OTHERS
33           THEN
34              DBMS_OUTPUT.put_line (l_cmd);
35              DBMS_OUTPUT.put_line (SQLERRM);
36        --RAISE;
37        END;
38     END LOOP;
39  END;
40  /

Result:

LOGS.LOG1                                                      may contain email addresses
OBR1_IN.E_MAIL                                                 may contain email addresses
OBR2_IN.E_MAIL                                                 may contain email addresses
...
RPRT_OP.E_MAIL                                                 may contain email addresses
RPRT_PO.E_MAIL                                                 may contain email addresses
PL/SQL procedure successfully completed.
SQL>

1. Use quoted identifiers to ensure that your identifiers have the correct case (rather than using LOWER and unquoted identifiers which Oracle will implicitly convert back to upper-case, which seems pointless);
2. You can also check other data-types including CHAR, NVARCHAR2 and NCHAR; and
3. Ensure that DBMS_OUTPUT is enabled:

Like this:

DECLARE
l_cmd   VARCHAR2 (2000);
l_found INTEGER;
BEGIN
DBMS_OUTPUT.ENABLE();
FOR eachcol IN (
SELECT owner,
table_name,
column_name
FROM   all_tab_cols
WHERE  data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR')
AND    owner = 'SEARCHSCHEMANAME'
ORDER BY
table_name,
column_name
)
LOOP
l_cmd := 'SELECT COUNT(*)'
|| ' FROM   "' || eachcol.owner || '"."' || eachcol.table_name || '"'
|| ' WHERE  "' || eachcol.column_name || '" LIKE ''%@%.%'''
|| ' AND    ROWNUM = 1';
EXECUTE IMMEDIATE l_cmd INTO l_found;
IF l_found > 0 THEN
DBMS_OUTPUT.PUT_LINE(
RPAD (eachcol.owner || '.' || eachcol.table_name || '.' || eachcol.column_name, 92)
|| ' may contain email addresses'
);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(l_cmd);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END;
/

fiddle