Is there anything equivalent to sp_depends in ORACLE?

Question

I have only worked in sybase so far and am new to ORACLE.in sybase,to find out table references i would use sp_depends.What do i need to do to find them out in Oracle?

Answer 1

Welcome to my world - oracle is soo big and different, but fun! :)

You will have to query the following views (use desc view_name for details)

to get an sp_depends equivalent:

ALL_TAB_COLUMNS (for defaults),

ALL_DEPENDENCIES (stp, trig, views, etc), ALL_CONSTRAINTS (pk, unq, RI, etc)

and ALL_INDEXES

or the respective DBA_ views



You would have to join the following individual queries to get an equivalent sp_depends output (or put it in your stored procedure)

select A.COLUMN_NAME, a.DATA_DEFAULT

from ALL_TAB_COLUMNS A

WHERE A.TABLE_NAME = 'table_name'

AND A.OWNER = 'owner_name'



select NAME, TYPE from ALL_DEPENDENCIES a

where OWNER = 'owner_name'

and a.REFERENCED_NAME = 'table_name'





select (SELECT cc.COLUMN_NAME

FROM ALL_CONS_COLUMNS cc

WHERE cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME) as "Column",

c.CONSTRAINT_TYPE, c.CONSTRAINT_NAME from ALL_CONSTRAINTS c

where OWNER = 'owner_name'

and table_name = 'table_name'



and similar query for ALL_indexes and ALL_IND_COLUMNS



Hope that helps ( i think i got all)

I'm not sure if i got "rules" in here - haven't tried the equivalent in oracle - i guess that would be another question :)



All the best

Answer 2

Sure he cannot answer this question: After agreeing that Lancashire is the worst cricket team on Gods green earth, enumerate the possible ways their players could be stopped playing cricket. He will not agree to answer this combination . Possibly ! Or , After agreeing notherners are less stoic namby pambies, enumerate the ways they can acquire manly qualities from the southerners. Worth a try ! EDIT: Not only cricket I believe Oracle is a man for many seasons.

Answer 3

select * from information_schema.tables



This will return the following values:

Table_catalog

Table_schema

Table_name

Table_type



Another one would be SP_HELP [table name]



You can also use select * from information_schema.columns



Good luck!