CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
Nvr
Nvr

Reputation: 171

How to check particular data in all schema

Is this possible to find particular data from all schema in sql developer. My search should be like "Find database object" option in sql developer.

Upvotes: 0

Views: 400

Answers (1)

Justin Cave
Justin Cave

Reputation: 231781

Is it possible? Sure. But it will be terrifically expensive in anything other than a toy schema.

Most likely, you'd need to use dynamic SQL (you could probably accomplish the same thing with an XML query). A simplistic implementation would do something like

DECLARE
  l_search_string varchar2(1000) := 'foo';
  l_sql           varchar2(4000);
  l_cnt           integer;
BEGIN
  for c in (select *
              from user_tab_columns
             where data_type in ('CHAR', 'VARCHAR2'))
  loop
    l_sql := 'SELECT COUNT(*) FROM ' || c.table_name ||
             ' WHERE ' || c.column_name || ' = :1 ';
    EXECUTE IMMEDIATE l_sql
       INTO l_cnt
      USING l_search_string;

    IF( l_cnt > 0 )
    THEN
      dbms_output.put_line( l_search_string || ' found in column ' ||
                              c.column_name || ' in table ' ||
                              c.table_name );
    END IF;
  end loop;
END;

You could make this more efficient by constructing a single COUNT query per table rather than per column but that is going to be a more complicated statement to build. You could do a similar process for a l_search_number or a l_search_date searching numeric or date columns. You could make the code more robust by handling case-sensitive table or column names (I'm avoiding double-quoting everything for simplicity here). And you can do something more useful than writing the result to dbms_output.

From a performance standpoint, though, you're going to have to read essentially every row in every table in the database to do this which is going to be exceedingly slow in most real systems. Generally, this is also something that doesn't make a lot of sense to want to do. If you're searching for a particular string that you know is a person's name, for example, unless your schema is completely terribly designed you can almost certainly look at the schema and identify the handful of tables and columns that might hold a person's name. It doesn't make sense to search every column in every table on the off chance that someone is storing names in the model column of the car table.

Upvotes: 2

Related Questions