-1

I have an issue with composite primary keys. Having the name of a table, I need to know which columns are foreign keys and I need to know the names of the tables they relate to. Now this would be easy if it weren't for the fact that the related table have a composite primary key.

sample:

Table Fruits
[pk] FruitId
[pk] OwnerId
     Name 

Table Desserts
[pk] DessertId
     Name
[fk] FruitId
[fk] OwnerId

Given the above, I need to query sql "Give me a list of columns in table Desserts that have a fk relationship and tell me the name of the table and column it relates to.

Thanks for any help provided!

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
Mr W
  • 597
  • 1
  • 7
  • 22
  • 1
    @DanBracuk But it says SQL Server in the title – Radu Gheorghiu Nov 09 '15 at 16:08
  • Although the accepted answer in http://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table wasn't enough, the answer provided by DekDev in the same question did get me the details of composite keys. – Mr W Nov 10 '15 at 08:05

1 Answers1

2

Use this command to get details, which returns the table name and its primary key, the foreign key table name, the column name, and a lot of other details:

EXEC sp_fkeys 'MyTable'

See How can I list all foreign keys referencing a given table in SQL Server?

Community
  • 1
  • 1
user8128167
  • 6,929
  • 6
  • 66
  • 79
  • Sorry no. this didn't return anything for my table – Mr W Nov 10 '15 at 08:04
  • What database do you have selected in SQL Server Management Studio? If it is set to the default "master" database, then when you execute the above command it will return nothing. Make sure you select your database. – user8128167 Nov 10 '15 at 17:29