5

I'm on SQL Server 2014 and I have a User table:

+----+------+-----+
| ID | Name | ... |
+----+------+-----+
| 11 | John | ... |
| 12 | Jack | ... |
| .. | .... | ... |
+----+------+-----+

And I have many tables which references this one. Can I get a list of all tables which references this table? And, in particular, can I get a list of all tables which references a particular row on this table?

I will then need to update all references to User 12 to User 11.

Giovanni Lovato
  • 465
  • 2
  • 5
  • 15

2 Answers2

7

When you say "a list of tables which reference this table" do you mean a list of tables that have a foreign key that reference your primary key? If so you can get a list of foreign keys that reference your table 'User' with the following query:

SELECT name as Foreign_Key
,schema_name(schema_id) as Schema_Name
,object_name(parent_object_id) as Table_Name
FROM sys.foreign_keys
WHERE Referenced_object_id = object_id('dbo.user','U');

if your table belongs to a different schema other than dbo then replace the schema name.

This query will give you all the referenced foreign keys to your table User. You will now know what joins you need to make in your update statement to maintain referential integrity.

Edit: woahhhhhh screw my answer, The best answer by db2 gives more detail in his query. How to Find the Foreign Key Associated with a Given Primary Key

Add a WHERE clause to his query before the ORDER BY clause for a specific table.

ex. WHERE o2.name = 'User'

Community
  • 1
Luke Pafford
  • 311
  • 1
  • 11
7
sp_help 'User'

This command will give you all constraints and dependencies on this table. Further, if you also want all the procedures which are dependent on this table, let me know.

Chaos Legion
  • 294
  • 2
  • 7