Snippet Name: List_Foreign_Key_References

Tags:

Created Date: 2025-06-16 13:12:41
Last Modified Date: 2025-05-31 10:31:41

Dependencies:

DBA Notes:

Sample Output:

Title: List Foreign Key References
Author: user
in SQL Server, how can i list the foreign keys that reference a specific table named  [dbamigration].[tblInstanceActivitiesClosed]

Author: ChatGPT
To list the foreign keys that reference a specific table in SQL Server, you can query the system catalog views. Here's a query that will give you the foreign keys that reference the `[dbamigration].[tblInstanceActivitiesClosed]` table:

```sql
SELECT 
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    cp.name AS ParentColumn,
    tr.name AS ReferencedTable,
    cr.name AS ReferencedColumn
FROM 
    sys.foreign_keys AS fk
INNER JOIN 
    sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN 
    sys.tables AS tp ON fkc.parent_object_id = tp.object_id
INNER JOIN 
    sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
INNER JOIN 
    sys.tables AS tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN 
    sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE 
    tr.name = 'tblInstanceActivitiesClosed'
    AND tr.schema_id = SCHEMA_ID('dbamigration');
```

This query will list all foreign keys along with the corresponding parent tables and columns that reference the `[dbamigration].[tblInstanceActivitiesClosed]` table. 

If you want more details or need to adjust the schema or table name, you can modify the query accordingly.