Thanks ,
let me rephrase it . I want to look for view in a ddl file and then replace it with target schema.VW_<old view name > .

For example,

CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR

to

CREATE VIEW OPSDM002.VW_MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.VW_TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR

Problem comes when in some lines view name doesn't have source schema as its prefix .

Here is the code I am using to fix it in vain

#cat view_add.shl
#!/bin/ksh
#####################################################
# use cut and awk to refine the view name
######################################################

if grep "CREATE VIEW" dim_copy.20080516.sql
then
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' dim_copy.20080516.sql > dim_copy.20080516.sql1
cat dim_copy.20080516.sql1 > dim_copy.20080516.sql
elif grep "CREATE VIEW" dim_copy.20080516.sql | grep -v OPSDM002
then
cat dim_copy.20080516.sql | grep -i "CREATE VIEW" | grep -v OPSDM002 | awk '{print "OPSDM002."$3 }' > dim_copy.20080516.sql1
sed -e '/^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/' dim_copy.20080516.sql1 > dim_copy.20080516.sql
else
echo "file does not have view in it "
fi

here is sh -x one

#sh -x view_add.shl
+ [[ -n 1 ]]
+ return
+ grep CREATE VIEW dim_copy.20080516.sql
CREATE VIEW MOUSE_UHC AS SELECT DT_SYS_ID ,DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR FROM DIM_COPY;
CREATE VIEW OPSDM002.TABLE_UHC AS SELECT DAY_ABBR_CD,LST_DAY_MO_IND,MONTH_NBR,FULL_DT,DAY_NBR,LOAD_DT,WEEK_NBR_YR,SEQ_NBR
+ sed -e /^CREATE VIEW/s/\(.*\.\)\(.*\)/\1VW_\2/ dim_copy.20080516.sql
+ 1> dim_copy.20080516.sql1
+ cat dim_copy.20080516.sql1
+ 1> dim_copy.20080516.sql

Thanks a lot for your effort ,

Daya