SQL Server – Identify Objects Missing Primary Key Constraints

Background

Forgot the NoSQL crowd for a minute; most OLTP databases are best defined based on a firm understanding of the system being designed.

What are the entities ( table ), which set of attributes uniquely identify a record ( primary key ), and how are they connected ( foreign keys ).

Primary Key

Outline

  1. sys.objects
    • User Tables
      • Type = ‘U’
    • Microsoft Tables
      • is_ms_shipped=1
  2.  sys.key_constraints
    • Column
      • parent_object_id
        • Refers to table
      • type
        • PK => Primary Key
        • UQ => Unique Constraint
  3. sys.indexes
    • Column
      • object_id
        • Refers to table
      • is_unique
        • Unique Index

Code


select 

              [schema]
                = tblSS.[name]

            , [table]
                = tblSO.[name]

            , [uniqueConstraint]
                = tblSKCUQ.[name]

            , [index]
                = tblSI.[name]

            , [indexIsUnique]
                = tblSI.[is_unique]

            , [indexIsUniqueConstraint]
                = tblSI.[is_unique_constraint]

from  sys.schemas tblSS

inner join sys.objects tblSO

        on tblSS.[schema_id] = tblSO.[schema_id]

left outer join sys.key_constraints tblSKPCPK

        on tblSO.[object_id] = tblSKPCPK.[parent_object_id]

        and tblSKPCPK.[type] = 'PK'

left outer join sys.key_constraints tblSKCUQ

        on tblSO.[object_id] = tblSKCUQ.[parent_object_id]

        and tblSKCUQ.[type] = 'UQ'

left outer join sys.indexes tblSI

        on tblSO.[object_id] = tblSI.[object_id]

        and tblSI.[is_unique] = 1

where tblSO.[type] = 'U'

and   tblSO.[is_ms_shipped] = 0

and   tblSKPCPK.[object_id] is null

order by
          tblSS.[name]
        , tblSO.[name]
        , tblSI.[name]
        , tblSKCUQ.[name]<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>

References

  1. Microsoft
    • SQL Docs
      • System Catalog Views
        • sys.key_constraints

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s