Technical Article

Design : Find and script out Missing Foreign Keys

,

Run script.

Confirm all results are what is needed.

The script will not suggest an FK if the PK is not on top of a single identity column.

Alter scripts are to help developers locate the issue and implement the changes into Dev and QA and finally after lots and lots of testing, into live.

***Do not run FK result scripts into live without regression tests.***

Bask in the glory of prevention of poor data quality.

select concat(S.name,'.',o.name) BaseTable,C.name BaseColumn , 
       concat(S2.name,'.',O2.name) as ReferenceTable,
       concat('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 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

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating