How to get FKs between different fields in one table?

Question

How can I get all the constraints from the DB where one field is referenced to another field in the same table?

This type of reference can strongly affect performance, so I want to find all these references in my DB and analyze them.

Example of the table structure:

CREATE TABLE MyTable(id uniqueidentifier NOT NULL PRIMARY KEY, ReferentID uniqueidentifier)

ALTER TABLE [dbo].MyTable  WITH CHECK ADD  CONSTRAINT [TEST_FK] FOREIGN KEY([ReferentID])
REFERENCES [dbo].MyTable ([Id])
GO

ALTER TABLE [dbo].MyTable CHECK CONSTRAINT [TEST_FK]
GO

score 1 · Answer 1 · edited Mar 03 '17 at 13:43

1

You can get all foreign keys from this procedure

EXEC sp_fkeys 'MyTable';

or You can use:

select * from sysobjects where xtype='F';

edited Mar 03 '17 at 13:43

Meer

2,765
2
19
28

answered Mar 03 '17 at 11:53

Manish Kumar

184
2
9

score 1 · Accepted Answer · edited May 23 '17 at 11:53

Starting from the query in this answer you can add a filter to get only FKs referencing the same table.

The resulting query should look like this:

SELECT  
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME --
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME --
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 

WHERE   KCU1.TABLE_NAME=KCU2.TABLE_NAME

How to get FKs between different fields in one table?

2 Answers2