LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script returns any duplicate foreign keys

    Misc Notes: Special thanks to user "raja" for suggesting this!
   
    Versions: SQL Server 2005
   
    Creation Date: June 1, 2007

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
-- Some sample tables that cause dupes:
--            NOTE: Don't run this in production; for illustrative purposes only
use tempdb
GO
IF OBJECTPROPERTY(OBJECT_ID('Parent'), 'IsTable') IS NULL
    CREATE TABLE Parent ( ParentId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ColA INT)
GO
IF OBJECTPROPERTY(OBJECT_ID('Child'), 'IsTable') IS NULL
    CREATE TABLE Child (ChildId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentId INT NOT NULL)
GO
IF OBJECTPROPERTY(OBJECT_ID('Child2'), 'IsTable') IS NULL
    CREATE TABLE Child2 (Child2Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentId INT NOT NULL, AnotherParentId INT NOT NULL)
GO
-- Dupes:
IF OBJECTPROPERTY(OBJECT_ID('FK_Child_Parent1'), 'IsForeignKey') IS NULL
    ALTER TABLE Child ADD CONSTRAINT FK_Child_Parent1 FOREIGN KEY (ParentId) REFERENCES Parent(ParentId)
IF OBJECTPROPERTY(OBJECT_ID('FK_Child_Parent2'), 'IsForeignKey') IS NULL
    ALTER TABLE Child ADD CONSTRAINT FK_Child_Parent2 FOREIGN KEY (ParentId) REFERENCES Parent(ParentId)
GO
-- Not dupe:
IF OBJECTPROPERTY(OBJECT_ID('FK_Child2_Parent1'), 'IsForeignKey') IS NULL
    ALTER TABLE Child2 ADD CONSTRAINT FK_Child2_Parent1 FOREIGN KEY (ParentId) REFERENCES Parent(ParentId)
IF OBJECTPROPERTY(OBJECT_ID('FK_Child2_Parent2'), 'IsForeignKey') IS NULL
    ALTER TABLE Child2 ADD CONSTRAINT FK_Child2_Parent2 FOREIGN KEY (AnotherParentId) REFERENCES Parent(ParentId)
GO

-- This is the main query that returns the dupes:
WITH TablesWithFKs AS (
    SELECT OBJECT_ID ( '[' + tc.TABLE_SCHEMA + '].[' + tc.TABLE_NAME + ']') AS ObjectId
        , '[' + tc.TABLE_SCHEMA + '].[' + tc.TABLE_NAME + ']' AS FullyQualifedObjectName
        , tc.TABLE_SCHEMA, tc.TABLE_NAME, cu.COLUMN_NAME
        , tc.CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc -- Returns child table info
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
        ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
)
SELECT * FROM TablesWithFKs t
WHERE EXISTS (
        SELECT * FROM TablesWithFKs sub
        WHERE sub.ObjectId=t.ObjectId
            AND sub.COLUMN_NAME = t.COLUMN_NAME
            AND sub.CONSTRAINT_NAME <> t.CONSTRAINT_NAME
)