0

I have a database with a lot of tables, the tables have a lot of foreign keys. Is there an easy way to view the other tables being referenced by the foreign key in a given table?

I tried "view dependencies" but this doesn't list all the references. I've also tried "script table as > create to" which has worked for some tables, but I lack sufficient permission for others.

Another roadblock is that the foreign keys in a given table do not have the same name as the primary key in the table being referenced, therefore even if I wanted to do a manual search (expand every table's columns) I would not be 100% sure I found the right table.

iliketolearn
  • 670
  • 3
  • 8
  • 25

1 Answers1

1

I think the following stackoverflow link have answers you are looking for. It has multiple queries I use the one specified by "Gustavo Rubio", please find the same query below

SELECT  obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM 
  sys.foreign_key_columns fkc
INNER JOIN 
  sys.objects obj ON obj.object_id = fkc.constraint_object_id
INNER JOIN 
  sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
INNER JOIN 
  sys.schemas sch ON tab1.schema_id = sch.schema_id
INNER JOIN 
  sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN 
  sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
INNER JOIN 
  sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
ORDER BY
  [table] ASC
Community
  • 1
  • 1
Rao Y
  • 99
  • 5