Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #346119] Sat, 06 September 2008 03:40 Go to next message
vijay838
Messages: 9
Registered: September 2008
Junior Member
how to find table name by using column values.i have more than 20000 tables in my db.i wrote a query like this
select table_name from all_tab_cols where column_name='contract_id'
when i execute this i query i am getting 10000 reacords and contract_id is not a primary key.plz help me.
Re: sql query [message #346121 is a reply to message #346119] Sat, 06 September 2008 03:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is it exactly you are after? The needle in the haystack?
Are you seriously considering to go searching for a record in a 20,000(?!) tables database that has a specific value in one of its columns?
Re: sql query [message #346126 is a reply to message #346121] Sat, 06 September 2008 06:25 Go to previous messageGo to next message
vijay838
Messages: 9
Registered: September 2008
Junior Member
that is my requirement.i think its impossible.is there any chance to do that......
Re: sql query [message #346128 is a reply to message #346126] Sat, 06 September 2008 07:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I take it you don't have 10,000 foreign keys to your contract-table...


Just out of curiosity: how on earth do you end up with 20,000 tables, and how come 10,000 of those refer to a contract?
What kind of data do you have that you have 10,000 different entities?
Re: sql query [message #346129 is a reply to message #346119] Sat, 06 September 2008 07:21 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear Vijay,
There could be n nof of tables having column name as "Contract_id" and that's the reason you are getting that many no of records.May i know where you are working?
Re: sql query [message #346135 is a reply to message #346119] Sat, 06 September 2008 07:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you might try to adjust such a script (it searches for all tables in current schema that contain character value of '1010240' in a column named 'sifra'; it won't display all tables that contain that column, but only those that contain this value stored in it (see the IF construct)):
DECLARE
  l_str VARCHAR2(500);
  l_cnt NUMBER := 0;
BEGIN
  FOR cur_r IN (SELECT u.table_name
                FROM user_tab_columns u, user_tables t
                WHERE u.table_name = t.table_name
                  AND u.column_name = 'SIFRA'
															)
  LOOP
    l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
             ' WHERE sifra = ''1010240''';
													
    EXECUTE IMMEDIATE (l_str) INTO l_cnt;

    IF l_cnt > 0 THEN
       dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);
    END IF;							
  END LOOP;
END;
Re: sql query [message #346202 is a reply to message #346135] Sun, 07 September 2008 04:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Littlefoot wrote on Sat, 06 September 2008 14:41

    l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
             ' WHERE sifra = ''1010240''';
													
    EXECUTE IMMEDIATE (l_str) INTO l_cnt;

    IF l_cnt > 0 THEN



Ouch..

Why select count(*) if you only want to know if at least one row is present?!
Do a select into and catch no_data_found and either add a where exists or catch too_many_rows
Re: sql query [message #346214 is a reply to message #346202] Sun, 07 September 2008 07:01 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Frank
Ouch
I hope it didn't hurt too much!

COUNT(*) is used because I needed it when writing this script. This, however, isn't the complete solution to the OP's problem; that's why I have said that he
LF
might try to adjust such a script
Previous Topic: All table_names, row_count and size
Next Topic: Creating Search function
Goto Forum:
  


Current Time: Fri Dec 09 15:26:35 CST 2016

Total time taken to generate the page: 0.11131 seconds