To find a particular email address in all the peoplesoft tables

I have assumed that the field which stores the actual email value, would have length more than 30 characters.

Once this procedure is executed, you can find all these tables stored in this table:
PS_TEST_SEARCH_REC


SELECT
CASE WHEN SUBSTRING (A.RECNAME,1,2)<>'PS'
THEN 'SELECT COUNT(1) FROM PS_'+A.RECNAME+ ' WHERE '+'UPPER('+(B.FIELDNAME)+')' +
'=UPPER(''' +'Ermsnotification@abc.com' + ''''+')'
ELSE 'SELECT COUNT(1) FROM '+A.RECNAME+ ' WHERE '+'UPPER('+(B.FIELDNAME)+')' +
'=UPPER(''' +'Ermsnotification@abc.com' + ''''+')' END FROM PSRECDEFN A, PSRECFIELDDB B, PSDBFIELD C
WHERE A.RECNAME=B.RECNAME
AND A.RECTYPE=0
AND B.FIELDNAME=C.FIELDNAME
AND ((C.LENGTH>30 AND C.FIELDTYPE=0) OR (C.FIELDTYPE=1))
AND A.RECNAME NOT LIKE '%AET'
AND A.RECNAME NOT LIKE '%TMP'
AND EXISTS(SELECT '1' FROM SYSOBJECTS WHERE NAME=CASE WHEN SUBSTRING (A.RECNAME,1,2)<>'PS'
THEN 'PS_'+A.RECNAME ELSE A.RECNAME END)