Re: Whole database search (solution)

improvement on my previous post--I got it all in one script so it is not a two step process of creating a script and running it.

You still have to hand edit the script to put in the database name, search string and other parameters.

#!/bin/sh
# search whole database for text string
# >> Must Hand Edit to set
# CONNECT STRING
# SEARCH STRING
# string length (DATA_LENGTH)
# table OWNER
#
# currently hard coded
# You can also restrict the search to tablenames matching a
wildcard--see
# uncomment the 'and table_name like ...' in the Where clause.

connectstr=SCOTT/TIGER_at_MYDB

generate_sql()
{
# generate Sql statements to search all varchar2 columns in all tables
for a string
echo "set feedback off"

sqlplus -s $connectstr <<EOF
  set pages 0
  set feedback off
  set linesize 300

• print the table name select 'select distinct '''||owner||'.'||table_name||''', '
• then the value ||owner||'.'||table_name||'.'||column_name ||' from '||owner||'.'||table_name ||' where lower( '||column_name||') like lower(' || '''%SEARCH STRING%'' ) '||';' -- from all_tab_columns where data_type like 'VARCHAR2' and DATA_LENGTH >= 5 and OWNER = 'OWNER'
  • and table_name like 'ABC%' / EOF }
    # pipe sql statements into second SQLPLUS
    generate_sql | sqlplus -s $connectstr