Design : Find and script out Missing Foreign Keys

  • MadAdmin

    SSChampion

    Points: 11260

    Comments posted to this topic are about the item Design : Find and script out Missing Foreign Keys

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • rcerney 63319

    SSC Veteran

    Points: 206

    Great script! Just a note that this script as written will only work with 2012+ due to the concat function. It could be easily changed though if your database version is lower. This works on my 2008 r2 instances.

    SELECT

    ( S.name + '.' + O.name ) BaseTable, C.name BaseColumn,

    ( S2.name + '.' + O2.name ) AS ReferenceTable,

    ( 'alter table ' + S.name + '.' + O.name + ' with check add constraint FK_' + O.name + '_' + O2.name + ' foreign key (' + C.name + ') references ' + S2.name + '.' + O2.name + '(' + IDC.name + ')' ) FKCreateStatement

    FROM

    sys.columns C

    INNER JOIN sys.identity_columns IDC ON (

    IDC.name = C.name OR C.name = OBJECT_NAME(IDC.object_id) + IDC.name

    ) AND C.object_id <> IDC.object_id AND C.is_identity = 0 --exlude Columns which are identities

    INNER JOIN sys.objects O ON O.object_id = C.object_id AND O.is_ms_shipped = 0 AND O.type = 'u'

    INNER JOIN sys.schemas S ON S.schema_id = O.schema_id

    INNER JOIN sys.objects O2 ON O2.object_id = IDC.object_id AND O2.is_ms_shipped = 0 AND O2.type = 'u'

    INNER JOIN sys.schemas S2 ON S2.schema_id = O2.schema_id

    LEFT JOIN sys.foreign_key_columns FKC ON IDC.object_id = FKC.referenced_object_id AND FKC.referenced_column_id = IDC.column_id

    INNER JOIN (

    SELECT

    I.object_id, IC.index_id

    FROM

    sys.index_columns IC

    INNER JOIN sys.indexes I ON I.object_id = IC.object_id AND I.index_id = IC.index_id

    WHERE

    I.is_primary_key = 1

    GROUP BY

    I.object_id, IC.index_id

    HAVING

    COUNT(*) = 1

    ) SingleColumnPK ON IDC.object_id = SingleColumnPK.object_id

    WHERE

    FKC.referenced_object_id IS NULL AND C.name <> 'ID'

    ORDER BY

    1;

  • MadAdmin

    SSChampion

    Points: 11260

    rcerney 63319 (2/1/2016)


    Just a note that this script as written will only work with 2012+ due to the concat function. It could be easily changed though if your database version is lower. This works on my 2008 r2 instances.

    Cool.

    You can add the script and in title you can mention it is compatible with 2005+.

    Great that you find it useful.

    Take care.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice script, thank you.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Now this has been kind of handy, thanks again.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply