Oracle doubt

Results 1 to 3 of 3

Thread: Oracle doubt

  1. #1
    Join Date
    Dec 1969

    Default Oracle doubt

    Hi,<BR><BR>This might be stupid question !<BR><BR>We have round 100 tables in Oracle database. <BR>In most of the tables company_id is part of the key.<BR>I need to figure out how many tables has entry for company_id 223. <BR>Is there any quick way to identify the records other than running query "select * from tablename where company_id = 223" for each table?

  2. #2
    Join Date
    Dec 1969

    Default There could be some easier way than this, but

    i know this will work. do this in an anonymous block.<BR><BR>create a cursor with <BR>select object_name from user_objects where object type=&#039;TABLE&#039;<BR> now you have list of tables in your schema. inside the loop pass these names one by one to your query, while handling exception for those tables where you don&#039;t find any record.<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default Actually you could just grab all those

    tables that have that column like<BR><BR>SELECT DISTINCT table_name <BR>FROM user_tab_columns <BR>WHERE rtrim(column_name) = &#039;COMPANY_ID&#039;;<BR><BR>Remember its a case-sensitive check. Now like Sreenivas said, put this into a cursor and loop through and execute a dynamic SQL statement.<BR><BR>I&#039;ll get you started here. ; )<BR><BR>DECLARE<BR><BR>sql_stmt varchar2(1000);<BR><BR>CURSOR get_co_tables IS<BR> SELECT DISTINCT table_name <BR> FROM user_tab_columns <BR> WHERE rtrim(column_name) = &#039;COMPANY_ID&#039;;<BR><BR>Next loop thru them.<BR><BR>co_rec get_co_tables%ROWTYPE;<BR><BR>BEGIN<BR><BR> FOR co_rec IN get_co_tables LOOP<BR> BEGIN<BR><BR> sql_stmt := &#039;select count(*) from &#039;&#124&#124co_rec.table_name<BR> &#124&#124&#039; where company_id = 223&#039;;<BR><BR> ...blah blah blah<BR><BR><BR> END;<BR> END LOOP;<BR>END;<BR><BR>Now I didn&#039;t do the whole thing so you are going to have to finish it. ; )<BR><BR>One hint think about EXECUTE IMMEDIATE.<BR><BR>Good luck<BR>Pete

Posting Permissions

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