How to find tables with self referencing Foreign keys

Question

Hi is there any query to get all self referencing foreign keys . I need to get the all tables and the foreign keys in a database with sled referring and drop and create it .Any idea how to do it .?

Answer 1

You can use DMVs - more specifically sys.foreign_keys

SELECT
    fk.object_id AS FkId
    , fk.name AS FkName
    , fk.parent_object_id AS TableId
    , OBJECT_NAME(fk.parent_object_id) AS TableName
FROM
    sys.foreign_keys AS fk
WHERE
    fk.referenced_object_id = fk.parent_object_id