Re: foreign keys listing

use query on base tables sysobjects

select * from SysObjects where xtype='fk' ,p.u etc

syscolumns, etc....

i want to get Referential keys associated with  a table

list like
constraint_name | owner table | owner column | Referred table | referred column

how to do this
i got upto

select    a.constraint_name,    b.table_name,    a.r_constraint_name
from    user_constraints a,    user_constraints b
where    a.constraint_type = 'R'
and     a.r_constraint_name = b.constraint_name
and    a.table_name = 'tablename'

it list constraint name, tablename
but i dont know how to get column names ....

any ideas to proceed
thanks

Try the  sp_foreignkeys system stored procedure. More info here -
http://msdn2.microsoft.com/en-us/library/aa933404(SQL.80).aspx

murthysrn:

 

use query on base tables sysobjects

select * from SysObjects where xtype='fk' ,p.u etc

syscolumns, etc....


 

 

Is it oracle forum?

where is sysobjects, sp_foreignkeys in oracle!!!

jomet.varghese :


try this:

You need give Table  name to this Sql Script  [in place of TableNameGoesHere]


SELECT a.owner||'.'||a.table_name parent_table, b.column_name,
c.owner||'.'||c.table_name child_table, d.column_name child_pk
FROM all_constraints a, all_cons_columns b, all_constraints c, all_cons_columns
d
WHERE a.constraint_name = b.constraint_name
AND a.constraint_name = c.r_constraint_name
AND c.constraint_name = d.constraint_name
AND a.table_name = upper('TableNameGoesHere')
ORDER BY child_table;



I hope...using this script you can achieve your objective.

Thanks,

````````````


j@y

 --> Mark as Answer if this helps you.