Re: Dynamic SQL to query all columns on all tables

  • From: david wendelken <davewendelken@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 26 Apr 2005 13:27:25 -0700 (PDT)

So, what is the query supposed to show you when it retrieves the data?

Just the column value the string is in?  Every column of a qualifying datatype, 
if just one of the columns has that string in it?

Identifying information about the table, or the actual row?

Does *every* table have a primary key constraint or a unique key constraint?

Answers to the above questions will dramatically alter what you have to write 
and what you will see when it runs.

Your best bet may be to write a sql statement that will write one sql statement 
per table or table/column combination of interest into a spool file, then run 
the statements in the spool file.
If you aren't familiar with this approach, let me know and I'll dig up a sample.

Database views to take a look at are all_tab_columns, all_constraints and 
They have the raw data needed to construct queries that may meet your needs.


Other related posts: