How to use - sp_fkeys 1

[ptmcs]

hi,

does anybody know how to use sp_fkeys ?

I have a table that definitely has foreign keys
(can examine them in enterprise manager/table design)

but can't work out the syntax for sp_fkeys to
simply list all foreign keys the table has...
Yes I know it should be easy!!! and the online reference
tells me the syntax - but it won't work!

here's what Microsoft reckon.....

USE Northwind
EXEC sp_fkeys @pktable_name = N'Customers'

 

[dbomrrsm]

just tried your above example in my QA and it worked fine !!

[blue]DBomrrsm[/blue]

 

[ptmcs]

the example works ok in the demo database -
but not in our application database when trying
to interrogate a table, note the table owner
is not dbo:
eg:

USE amos
EXEC sp_fkeys @pktable_name = N'amos.componentunit'

returns nothing! when there are foreign keys!

 

[dbomrrsm]

just created a test and set up a new PK - FK relationship between two tables in my test db pk = product_id in product table and fk = picture_id in product picture table.

Ran:

USE test
EXEC sp_fkeys 'product'

Results

Test
dbo
PRODUCT
PRODUCT_ID
Test
dbo
PRODUCT_PICTURE
PICTURE_ID
1
0
0
FK_PRODUCT_PICTURE_PRODUCT
IX_PRODUCT
7

[blue]DBomrrsm[/blue]

 

[SQLSister]

Are you searching on the table that contains the primary key or the one which contains the foreign key? The sp is intended to be used on the primary key table.

Questions about posting. See faq183-874

 

[SQLSister]

I figured it out. YOu have to specify the owner in a differnt input variable.

EXEC sp_fkeys @pktable_name = N'table3',@pktable_owner	= N'test'

Questions about posting. See faq183-874

 

[dbomrrsm]

well done sis

[blue]DBomrrsm[/blue]

 

[ptmcs]

Thanks..... that works.....
Sybase - all is forgiven !! (just joking!)