Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    29

    Unanswered: query database columns

    Hi All,
    I want to query my database tables to find which of the table columns has value(s) equal to a particular value. I have close to 300 tables in the database with thousands of columns. I have a value like 02EA034680 and I want to find which of the columns in the database has that value or which the pattern matches.
    Need help on this ASAP
    Thanks
    'soga

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    write SQL to write SQL against USER_TAB_COLUMNS
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Posts
    29

    sql to write sql?

    Hi
    I don't get this 'write sql to write sql against user_tab_columns
    Please could u expantiate further
    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This script will return all tables which have the 'pogon' column value equal to '02EA034680'. Tweak it to extract information you need; I believe this might be a good starting point. You'll have to include the 'USER_TAB_COLUMNS' as you don't know the column name (which I knew, so I didn't need to query this view).

    Code:
    DECLARE
       str     VARCHAR2 (1000);
       l_cnt   NUMBER;
    BEGIN
       FOR cur_t IN (SELECT table_name FROM user_tables)
       LOOP
          BEGIN
             str :=
                   'SELECT COUNT(*) FROM '
                || cur_t.table_name
                || ' WHERE pogon = ''02EA034680''';
    			
             EXECUTE IMMEDIATE (str)
                          INTO l_cnt;
    
             IF l_cnt > 0
             THEN
                DBMS_OUTPUT.put_line (cur_t.table_name);
             END IF;
          EXCEPTION
             WHEN OTHERS
             THEN
                NULL;
          END;
       END LOOP;
    END;
    /

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT 'SELECT COUNT('||COLUMN_NAME||') FROM '||TABLE_NAME||';' FROM USER_TAB_COLUMNS;
    expand upon the template above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2007
    Location
    Switzerland
    Posts
    27

    extended version ...

    I took Littlefoot's example and added user_tab_column:

    DECLARE
    str VARCHAR2 (1000);
    l_cnt NUMBER;
    BEGIN
    FOR cur_t IN (SELECT table_name, column_name FROM user_tab_columns
    Where data_type in ('CHAR','VARCHAR2'))
    LOOP
    BEGIN
    str :=
    'SELECT COUNT(*) FROM '
    || cur_t.table_name
    || ' WHERE '|| cur_t.column_name ||'= ''02EA034680''';


    EXECUTE IMMEDIATE (str)
    INTO l_cnt;

    IF l_cnt > 0
    THEN
    DBMS_OUTPUT.put_line (cur_t.table_name);
    END IF;
    EXCEPTION
    WHEN OTHERS
    THEN
    NULL;
    END;
    END LOOP;
    END;
    /



    This should do the job !

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that you didn't understand Olusoga's question; 02EA034680 is NOT column name, but column value. Which column? It is unknown (and that's why it is necessary to loop through all columns for a particular table. In other words - nested loops).

  8. #8
    Join Date
    Nov 2007
    Location
    Switzerland
    Posts
    27

    sure I did

    well, I'd say it works. See the concats in the script. To be sure
    I suggest to use good old scott/tiger and replace '02EA034680' by 'CLERK' Another usefull thing would be add more values to the output:
    DBMS_OUTPUT.put_line ('Table'||cur_t.table_name||' Column '||cur_t.column_name);

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oops! Shame on me! I apologize, I should have paid more attention.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •