6 Replies Latest reply on Mar 23, 2010 8:58 AM by user11995516

    search string in DB

    724773
      Hi,

      Is there a Query to find a String within the Wohle Database, then i dont know which Table holds this Information.

      Regards

      MDK.
        • 1. Re: search string in DB
          sb92075
          Is there a Query to find a String within the Wohle Database, then i dont know which Table holds this Information.
          No, but you could write Dynamic SQL to complete a brute force search.

          Post Operating System (OS) name & version for DB server system.
          Post results of
          SELECT * from v$version

          Edited by: sb92075 on Feb 2, 2010 7:43 AM
          1 person found this helpful
          • 2. Re: search string in DB
            Vijayaraghavan Krishnan
            Hi,


            Hey checkout the below link, but be careful before using that procedure.

            [http://it.toolbox.com/blogs/david/search-for-a-given-string-in-all-fields-of-an-entire-schema-24074]
            [http://www.renetta.nl/libphp/search.php]

            Regards,
            Vijayaraghavan k
            • 3. Re: search string in DB
              724773
              Hi,

              thanks this is what i was looking for.
              regards.

              MDK.
              • 4. Re: search string in DB
                user11995516
                Since I have a new website, please update the link [http://www.renetta.nl/libphp/search.php] to [http://www.renetta.nl/kennisbank/oracle/database-search?lang=en]

                Cheers,

                Rene

                Edited by: user11995516 on 23-mrt-2010 2:03
                • 5. Re: search string in DB
                  Girish Sharma
                  Rene,

                  I check the link and i found useful in one of our application. Can you post the another new script for searching in this case. Supose there are 1000 tables in a schema. I want to search a candidate name and father name, simantenously; i mean searching value will be in two different columns. Even till now i have'nt run your code, but if could possible; please post here/your site.

                  Regards
                  Girish Sharma
                  • 6. Re: search string in DB
                    user11995516
                    Hi Girish,

                    Sorry for my late reply. It seems I don't get notified with new posts...
                    My script defines if a search string is a varchar or a number. If it is a number, all numeric columns in that schema are searched for that value.
                    If it a (var)char, all columns with %CHAR% datatype (except 'VARCHAR2_TABLE') are searched for that value. LOBs are excluded from the script, but maybe I will build that in a while. Time is my biggest enemy ;-)

                    I can understand the need to search double values in one search, but the chance to find one value is bigger than finding two different values in one search.
                    I will try to explain here:

                    table tab1 in schema rene
                    col1 name surname
                    ___ _____ _______
                    1 john doe
                    2 johnny depp
                    3 johnny crockett
                    4 rene zijlstra

                    Executing search.sql in schema rene for string 'john' will return: RENE.TAB1.NAME: 1 (schema, tablename, number of values)
                    searching for string "johnny" in schema RENE returns: RENE.TAB1.NAME: 2
                    This means is you search for a string in the database you manually have to query those tables.
                    It does not return the value in those columns or all columns of that table. You could let the script return rowid's if you want. Open the script, go to line 70 and 71 and replace

                    l_cnt_stmt varchar2(200) := 'select count(*) from "'||p_schema||'"."'||p_table||'" where lower('||p_column||') like :b3';
                    l_count number;
                    with
                    l_cnt_stmt varchar2(200) := 'select rowid from "'||p_schema||'"."'||p_table||'" where lower('||p_column||') like :b3';
                    l_count rowid;

                    You can make it tailor made to your own needs, just leave the header intact.

                    for the record: If you have a database schema with thousands of tables with milions of records, it will take a while. I advise you to do that in a quit moment or clone your database to another server and do the search there. That prevents you from being lynched ;-)

                    Regards,

                    Rene

                    Edited by: user11995516 on 23-mrt-2010 1:57