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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
alex
  • 25
  • 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)? – Jeroen May 27 '20 at 18:19
  • It could be "pointing" to multiple tables too... just saying. – The Impaler May 27 '20 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. – alex May 27 '20 at 18:34
  • If it points to multiple tables then I would like to know all their names. – alex May 27 '20 at 18:35
  • 3
    SQL is a language defined by ISO/ANSI, and supported by a bunch of products. Which one are you using? – jarlh May 27 '20 at 18:39
  • I'm using Microsoft SQL – alex May 27 '20 at 18:42
  • Quick tip, OP does https://stackoverflow.com/a/12956348/419956 perhaps already help? – Jeroen May 27 '20 at 18:59
  • #Jeroen As I already mentioned "EXEC sp_fkeys 'TableName'" shows me OTHER tables that POINT at the table Order (children of the table Order). But there is no information about the parents. If I knew the origin of the foreign key "CategoryID" I would run that code on that table and view the table Order and column CategoryID, in the results of that query, but I don't know the name of that table. – alex May 27 '20 at 19:04
  • Amongst many other things `exec sp_help 'Order'`enumerates foreign key relationships and which tables and columns they reference. Does that not work in this case? – AlwaysLearning May 27 '20 at 22:20

1 Answers1

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

Muhammed Nigil
  • 173
  • 1
  • 10
  • 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. – alex May 28 '20 at 12:45
  • Btw, if I add "and name='CategoryID'" to WHERE it obviously shows me nothing. – alex May 28 '20 at 12:48
  • Is it possible that a foreign key is not registered in sys.foreign_keys? – alex May 28 '20 at 12:55
  • @alex can you try removing the key and adding it again – Muhammed Nigil May 28 '20 at 13:05