Generic script for finding orphaned data in Microsoft SQL Server tables

Question

I have a Microsoft SQL Server 2012 database with a huge amount of tables (>100). I have also many many-to-many relation tables. All PK/FK relationships are made in the tables so that i know in which table which column belongs to which parent or child tables columns. I wanted to ask if it is possible to have a generic script which check for a all of those or even given tables if there are orphaned items and show them me. I mean if there is any reference to a parent or child item which does not exist longer (there where errors in the Cascade Delete configuration of some tables and we need to check now the data qualit :( ).

Update

I think i couldnt explain my requirement corretly. I dont want to see the references, i want to see if any data is referencing an child item or a child item is referencing a parent item which does not exist anymore.

I'm not sure I understand the question: FKs by default enforce the integrity... — Lucero, Dec 16 '16 at 06:48
Possible duplicate of [How can I list all foreign keys referencing a given table in SQL Server?](http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server) — Mr. Bhosale, Dec 16 '16 at 07:34
@Mr. Bhosale: Thank you, but this shows me only the constraints. I want to see if any data is referencing an child item or a child item is referencing a parent item which does not exist anymore. – STORM 42 mins ago — STORM, Dec 16 '16 at 09:39
Lucero is right. It is not possible to have orphaned items when having correct PK/FK relationships. — STORM, Dec 17 '16 at 10:12

score 0 · Answer 1 · answered Dec 16 '16 at 06:47

0

Try This for get all constraints :

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

answered Dec 16 '16 at 06:47

Mansoor

4,061
1
17
27

Thank you for your answer, but this shows me only the constraints. I want to see if any data is referencing an child item or a child item is referencing a parent item which does not exist anymore. – STORM Dec 16 '16 at 08:56

score 0 · Answer 2 · answered Dec 16 '16 at 07:34

Try This.

            SELECT  obj.name AS FK_NAME,
                sch.name AS [schema_name],
                tab1.name AS [table],
                col1.name AS [column],
                tab2.name AS [referenced_table],
                col2.name AS [referenced_column]
            FROM sys.foreign_key_columns fkc
            INNER JOIN sys.objects obj
                ON obj.object_id = fkc.constraint_object_id
            INNER JOIN sys.tables tab1
                ON tab1.object_id = fkc.parent_object_id
            INNER JOIN sys.schemas sch
                ON tab1.schema_id = sch.schema_id
            INNER JOIN sys.columns col1
                ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
            INNER JOIN sys.tables tab2
                ON tab2.object_id = fkc.referenced_object_id
            INNER JOIN sys.columns col2
                ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

Thank you for your answer, but this shows me only the constraints. I want to see if any data is referencing an child item or a child item is referencing a parent item which does not exist anymore. — STORM, Dec 16 '16 at 08:56

Generic script for finding orphaned data in Microsoft SQL Server tables

2 Answers2