Finding Tables and ViewsUsing a Text String (4269665)

Finding Tables and ViewsUsing a Text String

I have a column called sourceid which is used throughout my database which contains hundreds of tables and views.  I want to fine all tables and views that contain a sourceid = "PE_DM_MW".
 
How do I complete this using Toad?

It has been determined that Toad for Oracle does not have a feature to do this request.
However, this pl/sql block will do the trick though. See steps below.

Go to the editor and turn on dbms_output, then run this.

declare

RowCount Number;

Qry Varchar2(300);

Cursor Tables is

select owner, table_name, column_name

from dba_tab_columns

where column_name = 'SOURCEID';

begin

for TableRec in Tables loop

begin

Qry := 'select count(*) ' ||

'from ' || TableRec.Owner || '.' || TableRec.Table_Name || ' ' ||

'where ' || TableRec.Column_Name || ' = ''PE_DM_MW''';

execute immediate Qry into RowCount;

dbms_output.put_line(TableRec.Owner || '.' || TableRec.Table_Name || ' ' || To_Char(RowCount) || ' Rows.');

exception

when no_data_found then

null;

when others then

dbms_Output.put_line(qry);

end;

end loop;

end;