How to write a query which finds all circular references when a table references itself?
This calls for a recursive CTE:
WITH FindRoot AS
(
SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path
FROM dbo.MyTable
UNION ALL
SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX))
FROM dbo.MyTable P
JOIN FindRoot C
ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
)
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId
AND R.ParentId <> 0;
See it in action here: SQL Fiddle
Update:
Added distance to be able to exclude all self cycles (see ypercube's comment):
WITH FindRoot AS
(
SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path, 0 Distance
FROM dbo.MyTable
UNION ALL
SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)), C.Distance + 1
FROM dbo.MyTable P
JOIN FindRoot C
ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
)
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId
AND R.ParentId <> 0
AND R.Distance > 0;
SQL Fiddle
Which one you should use depends on your requirement.
SELECT RC.CONSTRAINT_NAME FK_Name
, KF.TABLE_SCHEMA FK_Schema
, KF.TABLE_NAME FK_Table
, KF.COLUMN_NAME FK_Column
, RC.UNIQUE_CONSTRAINT_NAME PK_Name
, KP.TABLE_SCHEMA PK_Schema
, KP.TABLE_NAME PK_Table
, KP.COLUMN_NAME PK_Column
, RC.MATCH_OPTION MatchOption
, RC.UPDATE_RULE UpdateRule
, RC.DELETE_RULE DeleteRule
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KF.TABLE_NAME = KP.TABLE_NAME