0

I have a legacy Microsoft SQL Server database with many tables. I have a foreign key in table Order called CategoryID. I cannot find the table column the foreign key CategoryID is pointing to.

I do not have this foreign key in "Keys" folder.

All scripts that I found online give me the information about the tables which point at the table "Order" and its columns(children of the table "Order"), but they all do not give information about the parent tables.

Is there a script that gives the name of the table and column that foreign key is pointing at?

The foreign key

Improve this question
9
  • 5
    This likely/might depend on the specific RDBMS you're using (Microsoft, Oracle, Postgres, etc.). Could you add to the post which it is (e.g. as a tag)? Commented May 27, 2020 at 18:19
  • It could be "pointing" to multiple tables too... just saying. Commented May 27, 2020 at 18:34
  • I use SQL database (not MySQL). I added the tag when I posted the question. Now I added "SQL" to the text of the question. Commented May 27, 2020 at 18:34
  • If it points to multiple tables then I would like to know all their names. Commented May 27, 2020 at 18:35
  • 3
    SQL is a language defined by ISO/ANSI, and supported by a bunch of products. Which one are you using? Commented May 27, 2020 at 18:39

1 Answer 1

Reset to default
2

This script should help you 

 SELECT OBJECT_NAME(parent_object_id) AS [FK Table], name AS [Foreign Key], OBJECT_NAME(referenced_object_id) AS [PK Table] FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('Database.YourSchema.YourTableName');

you could try this too

SELECT OBJECT_NAME(f.parent_object_id) AS [FK Table], f.name AS [Foreign Key], c.name AS foreignColumnName, OBJECT_NAME(f.referenced_object_id) AS [PK Table] FROM sys.foreign_keys f inner join sys.foreign_key_columns fk on f.parent_object_id = fk.parent_object_id and f.referenced_object_id = fk.referenced_object_id inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id WHERE f.parent_object_id = OBJECT_ID('Database.YourSchema.YourTableName');

Table Structure

Result

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you very much. The code works. BUT the problem is (as I mentioned above) THIS particular foreign key (CategoryID) is not included in the folder "Keys" of this table. So your code shows all other foreign keys except that particular one. (Please, see the screenshot attached to the initial post). CategoryID is a foreign key, and NOT a foreign key (not included in the "Keys") at the same time. Could you please rewrite your code for SPECIFIC foreign key, not ALL foreigh keys. Thank you.
Btw, if I add "and name='CategoryID'" to WHERE it obviously shows me nothing.
Is it possible that a foreign key is not registered in sys.foreign_keys?
@alex can you try removing the key and adding it again

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.