Find which tables and columns contain trailing spaces

If you need to get rid of trailing spaces in a database, you should first find out which table columns contain them. We have a little script to help you do that.

If the script finds any trailing spaces, it prints the owner name, table name, and column name that contains the spaces. You can easily modify the script to search for any predefined string in the database tables. Of course, you'll need to replace the list of tables ('TAB1','TAB2',...,'TABN') with a valid list (or remove the condition for TABLE_NAME). To view the results of dbms_output, you may also need to issue the following command:

Set serveroutput on

Without further ado, here's the script:

DECLARE
l_query    LONG;
l_tab_name VARCHAR2(30);
l_col_name VARCHAR2(30);
l_owner    VARCHAR2(30);
l_str      VARCHAR2(100) := ' ';
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO( '%'||l_str);
for x in (
select OWNER, TABLE_NAME, COLUMN_NAME
   from ALL_TAB_COLUMNS
   where TABLE_NAME in ('TAB1','TAB2',...,'TABN')
   and DATA_TYPE in ('CHAR', 'VARCHAR2')
                )
loop
l_query := 'select ''||x.owner||'',''||x.table_name||'', ''||
   x.column_name||'' from '||x.owner||'.'||
      x.table_name||' where '||x.column_name||
      ' like userenv('client_info') and rownum = 1';
begin
EXECUTE IMMEDIATE l_query into l_owner,l_tab_name,l_col_name;
dbms_output.put_line('found in '||l_owner||'.'||l_tab_name||'.'||l_col_name);
exception
   when NO_DATA_FOUND then
      NULL;
end;                               
end loop;
END;