Looking for tables/columns that contain specific substring

Question

Is there any way to query an entire Oracle database using sql or plsql to return a list of all tables/columns that contain the specific substring? Note that I want to look for the substring within the data (not within the table/column names themselves).

I have an issue where a character looks like a space, but is actually a different character. I have proven this out by taking the substring text and putting it through a hexadecimal converter. I need to be able to locate everywhere in the database this character exists, so I can replace it with an actual space.

Any thoughts?

You can use ALL_TAB_COLUMNS to search in column and tables using like — HaveNoDisplayName, Dec 03 '14 at 01:07
possible duplicate of [Oracle Search all tables all columns for string](http://stackoverflow.com/questions/6389666/oracle-search-all-tables-all-columns-for-string) — Gerrat, Dec 03 '14 at 01:30
Thanks Gerrat, that gave me what I was looking for, and can fix my customers data appropriately now. — Colby Williams, Dec 05 '14 at 01:33

score 2 · Answer 1 · answered Dec 03 '14 at 01:09

2

If you're just looking for table and column names, you can use the view: ALL_TAB_COLUMNS:

SELECT owner, table_name, column_name FROM all_tab_columns 
WHERE table_name LIKE UPPER('%search_string%') 
OR column_name LIKE UPPER('%search_string%');

If you want to search additional places for names, you could use the ALL_OBJECTS view:

SELECT owner, object_name, object_type 
FROM all_objects WHERE object_name LIKE UPPER('%search_string%') ;

answered Dec 03 '14 at 01:09

Gerrat

28,863
9
73
101

ALL_TAB_COLUMNS will give me table and column names, but I need to be able to look for a specific substring within the data held in the columns, and then have a list of all the table/column names that have data stored in them that contain the specific character – Colby Williams Dec 03 '14 at 01:22
...so you want to go though every column in every table that has character like data and search for a string? You need to write a stored procedure. Grab table and column names from ALL_TAB_COLUMNS, and compose a dynamic sql statement that searches each string-like column for your search string. Have Fun. – Gerrat Dec 03 '14 at 01:27
I edited the OP to clarify that the request is to search data rather than column names as originally stated. – Robert N Dec 05 '14 at 13:40

score 0 · Answer 2 · answered Dec 03 '14 at 01:13

0

You can use ALL_TAB_COLUMNS table to search in column and tables using like.

answered Dec 03 '14 at 01:13

HaveNoDisplayName

8,291
106
37
47

Looking for tables/columns that contain specific substring

2 Answers2