-1

I'm having a bit of trouble finding how some tables are joined across databases due to system privileges/restrictions (but due to the virus admins are off).

Essentially, I'm trying to find what/where other tables a certain table's Primary Key is referenced (or being used as a Foreign Key).

Is there anyway to do this?

I've tried:

Select table_name, column_name

From information_schema.key_column_usage

But all I get is just a list of the table name and column name that I'm searching for...

Oh, btw, I'm using SSMS 18.0 (SQL).

Community
  • 1
  • 1
Mr Agent Smith
  • 25
  • 4
  • Does this answer your question? [How can I list all foreign keys referencing a given table in SQL Server?](https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server) – Piotr Palka Mar 26 '20 at 17:24

1 Answers1

0

Like this:

select object_name(parent_object_id) referencing_table, 
       object_name(referenced_object_id) referenced_table
from sys.foreign_keys

Technically a foreign key can reference a key other than the Primary Key, so if you want to exclude foreign keys referencing alternate keys, it would be:

select object_name(k.parent_object_id) referencing_table, 
       object_name(k.referenced_object_id) referenced_table
from sys.foreign_keys k
join sys.indexes i
  on i.object_id = k.referenced_object_id
 and i.index_id = k.key_index_id
where i.is_primary_key = 1
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67