locked
Orphan tables in the database (tables without any FK relations) RRS feed

  • Question

  • Hi Experts,

    I have a query which gives me Parent & Child tables based on FK relations.

    How to I find the remaining orphans tables (which does not have any relations to other tables) in the database?

    ---Query used to find Parent & Child relations

    Select
    object_name(rkeyid) Parent_Table,
    object_name(fkeyid) Child_Table,
    object_name(constid) FKey_Name,
    c1.name FKey_Col,
    c2.name Ref_KeyCol
    From
    sys.sysforeignkeys s
    left join sys.syscolumns c1 on ( s.fkeyid = c1.id And s.fkey = c1.colid )
    left join sys.syscolumns c2 on ( s.rkeyid = c2.id And s.rkey = c2.colid )
    Order by Parent_Table,Child_Table

    Thanks,

    Naveen


    Naveen J V

    Thursday, April 9, 2015 7:40 AM

Answers

  • E.g.

    SELECT  OBJECT_SCHEMA_NAME(T.object_id) ,
            T.name
    FROM    sys.tables T
            LEFT JOIN sys.foreign_keys FKC ON FKC.referenced_object_id = T.object_id
            LEFT JOIN sys.foreign_keys FKP ON FKP.parent_object_id = T.object_id
    WHERE   FKC.object_id IS NULL
            AND FKP.object_id IS NULL
    ORDER BY 1 ,
            2;

    • Marked as answer by Naveen JV Thursday, April 9, 2015 8:17 AM
    Thursday, April 9, 2015 8:03 AM

All replies

  • E.g.

    SELECT  OBJECT_SCHEMA_NAME(T.object_id) ,
            T.name
    FROM    sys.tables T
            LEFT JOIN sys.foreign_keys FKC ON FKC.referenced_object_id = T.object_id
            LEFT JOIN sys.foreign_keys FKP ON FKP.parent_object_id = T.object_id
    WHERE   FKC.object_id IS NULL
            AND FKP.object_id IS NULL
    ORDER BY 1 ,
            2;

    • Marked as answer by Naveen JV Thursday, April 9, 2015 8:17 AM
    Thursday, April 9, 2015 8:03 AM
  • Thanks Stefan. It is working as expected.

    Naveen J V

    Thursday, April 9, 2015 8:17 AM
  • select *
    from sys.tables t
    where NOT EXISTS (SELECT 1
    FROM sys.foreign_keys
    WHERE parent_object_id = t.object_id
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, April 9, 2015 9:04 AM