0

I am having a problem to list all the foreign keys in a database and show the related tables & the fields from each one.

I have tried this query:

SELECT OBJECT_NAME(parent_object_id) ,OBJECT_NAME(referenced_object_id) ,OBJECT_NAME(object_id) ,* FROM sys.foreign_keys

But this returns just the parent table and field. I need more information. Can anybody help me here?

Improve this question
7
  • Ok, so what's your question? What's stopping you? Why isn't what you've tried working? Commented Jan 16, 2020 at 11:16
  • My question is: HOW TO DO THIS? I've tried some commands but none off them worked for me... This command for example: select OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id), OBJECT_NAME(object_id), * from sys.foreign_keys This returns the parent table, but I need the referenced too... Commented Jan 16, 2020 at 11:21
  • 1
    That should all be in your "question", Rodrigo. "I need" is not a question, that's a demand/request we do the work for you. Edit your question to be something we can answer, along with everything else. Commented Jan 16, 2020 at 11:24
  • This is on my question now. I'm knew here dude. Commented Jan 16, 2020 at 11:30
  • You say you want "more information" What addition information do you want? What are you expecting to see here? Commented Jan 16, 2020 at 11:32

2 Answers 2

Reset to default
1

You can try the below query from the reference also the reference.

-- using sys tables to enumerate foreign keys -- returns 45 constraint rows SELECT f.name constraint_name ,OBJECT_NAME(f.parent_object_id) referencing_table_name ,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name ,OBJECT_NAME (f.referenced_object_id) referenced_table_name ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name ,delete_referential_action_desc ,update_referential_action_desc FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id ORDER BY f.name -- using INFORMATION_SCHEMA to enumerate foreign keys -- returns 45 constraint rows SELECT C.CONSTRAINT_NAME [constraint_name] ,C.TABLE_NAME [referencing_table_name] ,KCU.COLUMN_NAME [referencing_column_name] ,C2.TABLE_NAME [referenced_table_name] ,KCU2.COLUMN_NAME [referenced_column_name] ,RC.DELETE_RULE delete_referential_action_desc ,RC.UPDATE_RULE update_referential_action_desc FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY C.CONSTRAINT_NAME
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

To answer your (underlying) question, you can find everything you need in the following system tables:

INFORMATION_SCHEMA.KEY_COLUMN_USAGE INFORMATION_SCHEMA.TABLE_CONSTRAINTS INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Good luck!

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.