Can I search a DB to find the table a field is on? 1

[DPlank]

I'm new at Oracle, and the field I'm looking for should be on a table called CNRCUT01.

Except this table doesn't exist - all the table seem to begin with NCR. I've checked and it's not displacement of the tablename on my part.

Is it possible to search the schema for a particular field to find where it is? Sadly, the DBA is impossible to get hold of (or I'd have him by the throat right now!!)

TIA

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[oradba101]

Hi, DPlank

Try:

SELECT *
FROM dba_tab_columns
WHERE table_name = 'CNRCUT01'
AND column_name = '<your column name>'

Regards,


William Chadbourne
Oracle DBA

 

[DPlank]

Sadly, I get the message ora-00942 (table doesn't exist).

I've tried replacing the table_name with % to wildcard it, as I already know the CNRCUT01 doesn't exist in the schema (though it should, according to the diagram I have).

Most of the tables in the diagram begin CNR, but none of the tables in the actual DB do. They begin NCR, and it's sadly not the case that that's all that's different.

I cannot find what each table is meant to be. CNRCUT01 is meant to be the Customer table, but I have no way of telling what it's been named in the actual db. Hence the search for a field I know lives in the Customer Table. However, I'm almost willing to bet that the field has been renamed as well ... [grr]

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[SantaMufasa]

Dave,

Here is a script that shows you any tables in the database that contain all or part of a specific column name. (You will need to save the code to a script and run it from the script since the code contains an "ACCEPT...PROMPT" command; I saved the code to a script named "TT_242"):

col a heading "Tables containing|designated column" format a45
col b heading "Column name" format a30
accept col_name prompt "Enter (all or part of) the column name you are trying to locate in a table: "
select owner||'.'||table_name a, column_name b
from dba_tab_columns
where column_name like upper('%&col_name%')
/
********************************************************************************
SQL> @TT_242
Enter (all or part of) the column name you are trying to locate in a table: [b]item[/b]

Tables containing
designated column                             Column name
--------------------------------------------- -----------
SYS.GV_$RECOVERY_PROGRESS                     ITEM
SYS.V_$RECOVERY_PROGRESS                      ITEM
TESTNEW.S_ITEM                                ITEM_ID
TEST.S_ITEM                                   ITEM_ID

Let us know if this is what you wanted.

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.

 

[DPlank]

Feeling a bit dumb now...

I got the script set up as a .sql file, but SQL Plus says it can't run it. I may have saved the file in the wrong place (My Documents) and SP doesn't know where to look.

Is there a default location to place scripts?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[DPlank]

OK, got the script to run OK.

Unfortunately I'm still getting the 942 error. I'm not sure the dba_tab_columns exists..?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[SantaMufasa]

Dave,

Item 1 -- How to determine my default script directory. If you are using the SQL*Plus Windows GUI version:

1) right-click on the icon you use to invoke SQL*Plus.
2) click "Properties"
3) Observe (or change) "Start in:" directory value.

Item 2 -- "I'm not sure the dba_tab_columns exists...". It exists; it is a DATA DICTIONARY object to which all DBAs on the installation have access. For you to have access to it, your DBA must say:

grant select_catalog_role to <recipient>;

If <recipient> = PUBLIC, then everyone can see the data-dictionary objects; if <recipient> = <DPlank's Oracle username>, then you personally get to see the data ditionary objects.

If your DBA doesn't want you to see data-dictionary stuff, then you can change your script table reference from "DBA_TAB_COLUMNS" to "ALL_TAB_COLUMNS", to which you always have access.

Let us know if this information resolves your need.

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.

 

[DPlank]

OK, I can now get the script to run using ALL_TAB_COLUMNS instead of DBA_TAB_COLUMNS.

Although I get a response that looks like this :

SQL> @tt242
Enter (all or part of) the column name you are trying to locate in a table: CUS
old   3: where column_name like upper('%&col_name%')
new   3: where column_name like upper('%CUS%')

no rows selected

The code in the query is as follows (maybe I did something stupid?)

col a heading "Tables containing|designated column" format a45
col b heading "Column name" format a30
accept col_name prompt "Enter (all or part of) the column name you are trying to locate in a table: "
select owner||'.'||table_name a, column_name b
from all_tab_columns
where column_name like upper('%&col_name%')
/

Thanks for your help so far. I appreciate it!

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[SantaMufasa]

Dave,

Your code is fine. Your results are not what you wanted...The results mean that you do not have access to any table that contains the letters 'CUS' anywhere in the name of any columns in any table.

To show proof of concept, just re-run the script with a name fragment that you know exists in one of your tables.

Let us know your findings.

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.

 

[DPlank]

Tried it with COU_ISO as the fragment and it was successful in 25 tables.

So the script is fine, but the field CUS_ID is either not in the database or is on a table to which I cannot get access.

Right. Methinks a short chat with the DBA is in order...

Thanks for your help Dave! Star for your troubles.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!