Occasionally I’ve faced with the task to change the value of the primary key, say, in Employee table in SQL Server database. Everything would be OK if this table is not referenced from the other tables. So when I change the value in primary key EmployeeId column, I would break the foreign key constraints.
When you create a table you can specify a REFERENCES … ON UPDATE CASCADE clause, so all changes in primary key would be propagated to the underlying tables (look here for CREATE TABLE syntax and here for referential integrity constraints). But this is not my case, I have the default NO ACTION option. I want to know all tables and columns that are referencing my primary key. This can be done by one of the three ways:
- stored procedure
|
|
EXEC sp_fkeys N'Employee' |
UPDATE 2016-02-01:
If a table is not in a dbo schema, add a second parameter for owner.
- using system views from sys schema
|
|
SELECT OBJECT_NAME(constraint_object_id) AS [FK Name], OBJECT_NAME(referenced_object_id) AS [PK Table], COL_NAME(referenced_object_id, referenced_column_id) AS [PK Column], OBJECT_NAME(parent_object_id) AS [FK Table], COL_NAME(parent_object_id, parent_column_id) AS [FK Column] FROM sys.foreign_key_columns WHERE referenced_object_id = OBJECT_ID(N'Employee', N'U'); |
- using INFORMATION_SCHEMA views
|
|
SELECT c.CONSTRAINT_NAME, pk.TABLE_NAME AS [PK Table], pkcol.COLUMN_NAME AS [PK Column], fk.TABLE_NAME AS [FK Table], fkcol.COLUMN_NAME AS [FK Column] FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON c.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol ON pk.CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol ON fk.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME WHERE pk.TABLE_NAME = N'Employee'; |
These methods work perfectly when you’ve set foreign keys. There may be a situation when you have a logical reference, but it’s not implemented as a constraint. You can try to search for a column name. Maybe you will be lucky if you have strong naming conventions and you include PK column name in FK column name, i.e. primary key is EmployeeId, so the foreign key will be BossEmployeeId or ManagerEmployeeId. If you don’t follow this rule, you might have simple name like ManagerId and it will be more harder to guess where the reference might be.
This query could help you to find tables by column name.
|
|
SELECT OBJECT_NAME(c.object_id) AS [Table Name], c.name AS [Column Name], TYPE_NAME(c.system_type_id) AS [System Type], c.max_length AS [Max Length] FROM sys.columns c WHERE c.name LIKE '%EmployeeId%'; |
UPDATE 2016-04-13:
Modified version of the above query that include schema name.
|
|
SELECT SCHEMA_NAME(t.schema_id) AS [Schema Name], OBJECT_NAME(c.object_id) AS [Table Name], c.name AS [Column Name], TYPE_NAME(c.system_type_id) AS [System Type], c.max_length AS [Max Length] FROM sys.columns c JOIN sys.tables t on c.object_id = t.object_id WHERE c.name LIKE '%EmployeeId%'; |
