1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Find Column with Value '12345678' in DB

Discussion in 'SQL PL/SQL' started by Donnie28, Apr 22, 2014.

  1. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi All

    I need a query to search all the database tables and bring back the columns that hold a value of '12345678' in the database. I have tried the statement below but this just brings me back an invalid Column error

    select owner, table_name from all_tab_columns where column_value = '12345678';

    Any help on this would be very much appreciated

    Regards
    Donal
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    all_tab_columns - this view contains the description of columns of tables to which your user has an access.

    See describe : all_tab_col_statistics

    hardly it is possible to call such option of search is correct.
    What problem you need to solve?
     
  3. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Sergey thanks for the reply....I actually figured out the table and column that contained '12345678' so no longer need the query to search all columns in all tables for this value
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Well it was an interesting thought exercise -- even if I can't see much use for it (not even you any more).

    For the heck of it I made the following PL/SQL block that can actually do it. It makes some assumptions -- namely that this table wouldn't be in the SYS or SYSTEM schemas. Presumably you could eliminate more than that. Also, the value 123456768 can only be in a limited number of fields. The code only searches CHAR, VARCHAR2, and NUMBER. That said, I tested it and it seems to work.

    Code (Text):
    DECLARE
      CURSOR c_tabs_and_cols IS
        SELECT owner, table_name, column_name
        FROM   all_tab_columns
        WHERE  owner NOT IN ('SYS', 'SYSTEM')
        AND    data_type IN ('CHAR', 'NUMBER', 'VARCHAR2');

      v_sql      VARCHAR2(300);
      v_count    NUMBER;
    BEGIN
      FOR v_Lp IN c_tabs_and_cols LOOP
        v_sql := 'SELECT COUNT(*) FROM ' ||
                 v_Lp.owner || '.' || v_Lp.table_name ||
                 ' WHERE ' || v_Lp.column_name || '= ''12345678'' ';

        EXECUTE IMMEDIATE v_sql INTO v_count;

        IF v_count > 0 THEN
          DBMS_OUTPUT.PUT_LINE('Value is in ' || v_Lp.owner || '.' || v_Lp.table_name ||
                               ' column ' || v_Lp.column_name);
        END IF;
      END LOOP;
    END;
     
    Donnie28 likes this.
  5. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi Matthew

    Thanks for th reply and the effort its much appreciated :)

    Regards
    Donal