查找数据oracle中具有电子邮件的所有列(Find all columns that have emails in data oracle)

查找数据oracle中具有电子邮件的所有列(Find all columns that have emails in data oracle)

我会开始道歉，因为我不知道如何将问题转化为言辞。

我有一个约15000列的数据库，我想找到每个列中存储电子邮件。 我试过搜索栏目名称，但这没有帮助，因为有太多变化。

我想要做这样的事情

从all_tab_cols中选择column_name，其中数据如'%@%.com%

这是在oracle数据库上，但我通过tableau访问数据。

谢谢，Aayush

澄清：我想查找每个列中都有电子邮件地址的列。

I am going to start off by apologizing because I don't know how to put my problem into words.

i have a database with about 15000 columns and I want to find every column that has emails stored in it. I've tried searching through column names but that isn't helping because there is so much variation.

i want to do something like this

select column_name from all_tab_cols where data like '%@%.com%

this is on an oracle database but I am accessing the data through tableau.

Thanks, Aayush

Clarification: I want to find every column that has an email address in it.

最满意答案

这只会查找以@ something.com结尾的电子邮件地址，但您正在寻找类似下面的内容。 还有一些关于查找电子邮件地址的帖子，这很难做到：

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 '%@%.com%' 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;

This will only find email addresses that end with @something.com, but you are looking for something like what is below. There have been other posts about finding email addresses, it is very difficult to do:

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 '%@%.com%' 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;