how to find tables?

[3pmilk]

Hi all,

If I have a value, let's say "ABCDE," and I know this value
exists in a field of a table, but I don't know which table or field that this value is in. How do I go about using Oracle's data dictionary to search for the field and the table in which this value "abcde" is in?

I have only able to find all tables in the schema, shown blow, but couldn't go futher because I don't know how.
e.g:

I used this the following sql to find all the tables in the schema that I'm interested in.

sql:

select * from all_objects
where owner = "EVMS"
and object_name="Table";




Thanks,
Wendy

 

[SantaMufasa]

Wendy,

Not that it makes any difference to the solution that I propose for you, but to understand better the magnitude of your problem, how many tables are in your "universe", and what is the "median" and "average" number of rows and columns in your tables? Do you have DBA privileges for your Oracle log-in? How many schemas (Oracle Users) must you survey for the target value?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[3pmilk]

probably 10 to 15 tables...I don't know how many columns are in each table. What is the solution your propose?

I have done this before, it's a simple sql query...I just
don't remember how to do this..

 

[SantaMufasa]

Well, if you've done it before and you wish to do it again (on your own), then I can certainly give you some hints and some direction:

1) USER_TAB_COLUMNS contains the names of every column in every table and view that you own.
2) If you use SQL-writing-SQL or PL/SQL to loop through every table's VARCHAR columns looking for the string, "ABCDE", then voilà...The table and column containing your target value.

Is this a one-time shot, or do you need to do this type of search iteratively?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[3pmilk]

what table is user_tab_columns in.

This is a one time deal

 

[SantaMufasa]

Wendy,

USER_TAB_COLUMNS is an Oracle data-dictionary view. Essentially, you query it like any other table. It contains the names of each of the tables (and each table's columns) that you own. ALL_TAB_COLUMNS contains the same columns with the addition of "OWNER". That view shows the same rows as USER_TAB_COLUMNS with the addition of table/column information for everyone else's tables and views to which you have access:

desc USER_TAB_COLUMNS

Name                    Null?    Type
----------------------- -------- --------------
TABLE_NAME              NOT NULL VARCHAR2(30)
COLUMN_NAME             NOT NULL VARCHAR2(30)
DATA_TYPE                        VARCHAR2(106)
DATA_TYPE_MOD                    VARCHAR2(3)
DATA_TYPE_OWNER                  VARCHAR2(30)
DATA_LENGTH             NOT NULL NUMBER
DATA_PRECISION                   NUMBER
DATA_SCALE                       NUMBER
NULLABLE                         VARCHAR2(1)
COLUMN_ID                        NUMBER
DEFAULT_LENGTH                   NUMBER
DATA_DEFAULT                     LONG
NUM_DISTINCT                     NUMBER
LOW_VALUE                        RAW(32)
HIGH_VALUE                       RAW(32)
DENSITY                          NUMBER
NUM_NULLS                        NUMBER
NUM_BUCKETS                      NUMBER
LAST_ANALYZED                    DATE
SAMPLE_SIZE                      NUMBER
CHARACTER_SET_NAME               VARCHAR2(44)
CHAR_COL_DECL_LENGTH             NUMBER
GLOBAL_STATS                     VARCHAR2(3)
USER_STATS                       VARCHAR2(3)
AVG_COL_LEN                      NUMBER
CHAR_LENGTH                      NUMBER
CHAR_USED                        VARCHAR2(1)
V80_FMT_IMAGE                    VARCHAR2(3)
DATA_UPGRADED                    VARCHAR2(3)

Let us know how things go for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Actually your problem is in using mixed case in the word "Table": it should be in uppercase.

Regards, Dima

http://seminihin.narod.ru