Oracle/SQL question -- cross-table queries, I guess?

whuppins

I want to produce a list of all tables in my Oracle database that have a field called GROUP_ID. I know I can perform some cross-table (pseudo-)queries by using all_tables, e.g.:

select table_name from all_tables where table_name like '%foo%'; (to get a list of all tables whose names contain "foo")

Is there a way to extend this functionality to returning tables that contain certain fields? If so, is it possible to take this a step further and return all rows from said tables where the field in question is set to a certain value (in this case, 70)?

Infidel

select table_name from all_tab_columns where column_name like '%foo%' ?

You could probably edit the where clause to check that table_name.column_name for the actual values.

vonPoonBurGer

Infidel's SQL is correct, all_tab_columns will show the tables that have a column name containing that string. Note that it will only show you tables you have access to. If in doubt, connect as a user with DBA privileges and select from dba_tab_columns instead, that view will contain a list of all columns in the database. Don't forget that internally Oracle stores all identifiers (object names, column names, etc.) in upper case.

Edit: I take it back, this gets really ugly, really fast. The spoiler'd code below could work, but in order to display the data you need to assign the results to a variable. Since you're selecting a whole row, a row object of type TABLENAME%ROWTYPE would make sense, but then you need to know what table it's coming from so your INTO variable has the right format. Beyond that you have all sorts of potential issues with data type mismatches, e.g. comparing an integer value to a VARCHAR column whose column name just happens to match your LIKE statement. I could probably figure it out in PL/SQL given enough time, but I think it'd be far easier just to break it into two steps: query for the list of tables that have a column name, then run a separate query on those tables. If it's something you'd be doing regularly you could use a scripting language to glue the two steps together.

As for the second part of your question (automatically query those tables for a list of values), for that you'd need dynamic SQL. You can't write a plain static SQL statement for this because you don't know the name of one of the tables you want to query until after you've executed the SQL. For regular static SQL, Oracle needs all the table names at parse time, which happens prior to execution. If you use PL/SQL and EXECUTE IMMEDIATE, however, you can sidestep this problem:

DECLARE
   CURSOR c_tabs IS SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%FOO%';
BEGIN
   DBMS_OUTPUT.ENABLE(1000000);
   FOR p_tab IN c_tabs LOOP
      EXECUTE IMMEDIATE 'SELECT * FROM ' || p_tab.owner || '.' || p_tab.table_name || ' WHERE ' || p_tab.column_name || '= 70';
   END LOOP;
END;
/

xa52

select "select * from "|| table_name || "where [colname]=70;"
from all_tab_columns
where column_name='abc';

If this is just a one-off, and not something you need to script to run on a regular basis, it's easier* to write a query like the one above, it, copy the results, then run that.


(*Assuming you're like me and think pl/sql is a pain in the ass to be avoided whenever possible.)

whuppins

Thanks all! Got it sorted.