CODEWITHSUNDEEP
sqlsql-server
C.J.
C.J.

Reputation: 3527

Update multiple table without knowing the table name (due to a chain of Foreign key relationship)

I need to update one field for a few rows in one table (say, Table_A). However, I'm getting an error message saying conflict with the Foreign Key Constraint in Table_B.

So, I tried to update Table_B as well, turns out Table_B has Foreign Key Constraint with Table_C and Table_D; again, I tried to update Table_C and D, turns out they are conflicting with table_E, F, G, H, I, J, K etc. etc. and on and on.

I was told that such "chain" can go up to 20+ tables.

Additionally, I do not have access to the database schema, thus it is extremely difficult for me to determine which field in which table is the foreign key for the other table.

Currently, all I can do is manually checking each table, all the way from A-Z by using select * statement from the table that is showing in the error message. I'm wondering if there is any alternative to update these specific fields all across tables A till (whichever the last table) directly?

I'm using SQL Server 2005.

Upvotes: 0

Views: 55

Answers (1)

Anon
Anon

Reputation: 10908

This will give you the names of the tables and columns in your foreign keys

SELECT 
  OBJECT_NAME(fk.[constraint_object_id])    AS [foreign_key_name]
 ,OBJECT_SCHEMA_NAME(fk.[parent_object_id]) AS [parent_schema_name]
 ,OBJECT_NAME(fk.[parent_object_id])        AS [parent_table_name]
 ,pc.[name]                                 AS [parent_column_name]
 ,OBJECT_SCHEMA_NAME(fk.[parent_object_id]) AS [referenced_schema_name]
 ,OBJECT_NAME(fk.[referenced_object_id])    AS [referenced_table_name]
 ,rc.[name]                                 AS [referenced_column_name]
FROM [sys].[foreign_key_columns] fk
INNER JOIN [sys].[columns] pc ON 
  pc.[object_id] = fk.[parent_object_id] AND
  pc.[column_id] = fk.[parent_column_id]
INNER JOIN [sys].[columns] rc ON
  rc.[object_id] = fk.[referenced_object_id] AND
  rc.[column_id] = fk.[referenced_column_id]

How to best display and analyze the connection graph is a more subjective matter and will depend on the complexity of your schema.

Upvotes: 2

Related Questions