Duplicates and orphans are the two problems constraints were supposed to prevent and often don’t. A staging table has no primary key, a foreign key got disabled during a bulk load, or the relationship was never declared at all. Here’s the T-SQL to find both.
Duplicate rows
List duplicate groups on the columns that should be unique:
SELECT CustomerEmail, OrderDate, COUNT(*) AS copies
FROM dbo.Orders
GROUP BY CustomerEmail, OrderDate
HAVING COUNT(*) > 1
ORDER BY copies DESC;See every extra copy, keeping the lowest OrderID as the original:
WITH numbered AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY CustomerEmail, OrderDate ORDER BY OrderID) AS copy_no
FROM dbo.Orders AS o
)
SELECT *
FROM numbered
WHERE copy_no > 1;To remove them, change the final SELECT * to DELETE once you’ve reviewed the result, inside a transaction.
Watch three SQL Server behaviors:
| Behavior | Effect on a duplicate check |
|---|---|
| Case-insensitive default collation | ACME and acme count as one value |
| Trailing spaces ignored in comparisons | 'ACME' and 'ACME ' count as one value |
GROUP BY puts NULLs in one group | Rows with NULL keys show up as duplicates of each other |
TestGen’s migration docs describe the first two. If another system treats those values as different, group on UPPER(RTRIM(CustomerEmail)) to see what your collation is merging.
Orphaned foreign keys
Child rows whose parent doesn’t exist:
SELECT o.OrderID, o.CustomerID
FROM dbo.Orders AS o
WHERE o.CustomerID IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM dbo.Customers AS c WHERE c.CustomerID = o.CustomerID
);The IS NOT NULL filter matters. A NULL key is a missing reference, a different problem, and NOT EXISTS would report it as an orphan.
If a foreign key is declared but orphans exist anyway, the constraint was disabled or isn’t trusted:
SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name,
OBJECT_NAME(fk.parent_object_id) AS table_name,
fk.name AS constraint_name,
fk.is_disabled,
fk.is_not_trusted
FROM sys.foreign_keys AS fk
WHERE fk.is_disabled = 1 OR fk.is_not_trusted = 1;DBCC CHECKCONSTRAINTS ('dbo.Orders') WITH ALL_CONSTRAINTS; lists the rows that violate them. After you fix those rows, ALTER TABLE dbo.Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers; makes the constraint trusted again.
Undeclared relationships are the hard case. Many warehouses never declare foreign keys between facts and dimensions, so nothing in sys.foreign_keys tells you to check. You have to know the pairs.
On every table, without the scripts
The queries above cover the tables you thought of. DataOps TestGen covers the rest. It connects with a read-only SQL login and runs data profiling on every column, then:
- Duplicate Rows is generated from the profile, which picks the columns that look like a unique key. You can edit the column list to tighten it.
- Unique Values is generated for columns whose values were all distinct, in tables over 500 rows.
- Reference Match checks that each value, or combination of values, exists in a reference table: the orphan check. It’s not generated, so you add it once per relationship from the Test Definitions page.
Each runs as T-SQL inside SQL Server, keeps history, and alerts when counts change. The test types are documented under uniqueness tests and validity tests.
Open-source TestGen is free for one user, one connection, and one project. Start at the SQL Server install page, and see testing SQL Server without writing T-SQL for what else the profile generates.