INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Find PK use

Find PK use

(OP)
If I have a table, and a PK field in it, and I suspect this field is used in creating a view - how can I find which view is using this PK field from my table?

I am using TOAD

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Find PK use

I could be wrong, but I don't think Views have Primary Keys. You could try selecting the field(s) that comprise the PK with an AND condition so that you are sure that the views selected use all of the PK fields.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Find PK use

That is correct, views, as opposed to materialized views, do not have indexes. They are only stored queries that you invoke by selecting the name of the view. Any indexes used are the ones on the under laying tables that the view is selecting from.

Bill
Lead Application Developer
New York State, USA

RE: Find PK use

"used" how?

If I create a view like this:

CODE

CREATE OR REPLACE VIEW my_view
SELECT some_col
FROM your_table
WHERE pk_column = 2 

Is that "using" the PK? How about this one:

CODE

CREATE OR REPLACE VIEW my_other_view
SELECT some_col
FROM your_table t1
INNER JOIN your_other_table t2
WHERE t1.pk_column = t2.fk_column 

Or this one:

CODE

CREATE OR REPLACE VIEW my_other_other_view
SELECT pk_column
FROM your_table 

Frankly, since you're using Toad, it's likely to be quicker to go to the "Used by" tab of the table, find all the views the table is used in, and manually check them for PK use (whatever that may mean).

Why do you care anyway? Shouldn't you want the PK to be used wherever possible?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

RE: Find PK use

THE OPTIMIZER DECIDES how to use the indexes on the table(s). selecting form a view is no different that simply running the query that makes it us directly.

Bill
Lead Application Developer
New York State, USA

RE: Find PK use

(OP)
Thanks for all replies.
Let me modify my question (and forget about PK field).

Is there any way to find out which view(s) are using particular field from the table?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Find PK use

(OP)
ChrisHunt,
Let's say I have a TabelA with fields:
ABC, XYZ, and KLM

And there are some views created in Oracle.

Let's say I have a ViewOne with the script:
Select ABC, XYZ, KLM
From TableA
Where ...

In my mind, ViewOne is 'using' (referring to / mentioning / utilizing / employing / commissioning smile ) XYZ field from TableA
(there may be more technical term describing this 'using' expression)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Find PK use

I think I know what you want, but I can't provide the solution since I don't have an Oracle instance to verify my code. All databases, including Oracle, have system tables that contain information about Tables, Columns, and other database administrative information and metadata.

https://www.techonthenet.com/oracle/sys_tables/ind...

Since I am currently working with Vertica database, here's what I would code for Vertica:

SELECT COLUMN_NAME from VIEWS where COLUMN_NAME in ('a_column', 'b_column')

In this case, VIEWS is a system table and COLUMN_NAME is one of the columns in that system table.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Find PK use

I thought about using the following query

select owner,table_name
from all_tab_columns
where column_name in ('A_COLUMN','B_COLUMN');

but this is not a solid test because something like

create view my_view as
select a_column xxx
from my_table;

would show the column name as XXX, not as A_COLUMN.

The easiest way to do it is to find out what views reference your table and then look at the view to see if the column is used. Run the following select

SELECT A.Object_type,
A.Object_name,
A.owner view_owner,
B.Owner,
B.Object_type,
B.Object_name,
B.Object_id,
B.Status
FROM Sys.Dba_objects A,
Sys.Dba_objects B,
( SELECT Object_id, Referenced_object_id
FROM Public_dependency
START WITH Object_id IN (SELECT Object_id
FROM Sys.Dba_objects
WHERE Object_type = 'VIEW')
CONNECT BY PRIOR Referenced_object_id = Object_id) C
WHERE A.Object_id = C.Object_id
AND B.Object_id = C.Referenced_object_id
AND B.owner = 'MY_SCHEMA'
AND B.OBJECT_NAME = 'MY_TABLE'
AND A.Object_type = 'VIEW'
AND A.Owner NOT IN ('SYS', 'SYSTEM')
AND A.Object_name <> 'DUAL'
AND B.Object_name <> 'DUAL';


To see the source for the view type IN SQL*PLUS

SET LONG 32767
SET LONGC 100
SET PAGESIZE 0

SELECT TEXT
FROM ALL_VIEWS
WHERE VIEW_NAME = 'MY_VIEW'
AND OWNER = 'MY_SCHEMA';




Bill
Lead Application Developer
New York State, USA

RE: Find PK use

(OP)
Will give it a try, thou I don't have access to Dba_objects sad
Have to talk to my DBA...

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Find PK use

You can replace DBA_ with ALL_ which is a view for all the objects that you have access to. Sorry I should have used that instead

Bill
Lead Application Developer
New York State, USA

RE: Find PK use

(OP)
Thank you Beilstwh, tried your way but I did not get any results sad

But in TOAD, if I go to Search -> Object Search, I have a little GUI that allows me to search for anything anywhere. That's what I used. Problem solved thumbsup2

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Find PK use

I strongly suspect that TOAD only gave you the objects to which you have permissions. That is, the ALL_ views to which Bill referred.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close