Chris Miller Blog

RocketScientist's Miscellaneous Ramblings

Finding tables with no unique indexes

Same vendor, different issue.  How many tables in the database aren't really tables, but are actually just 2 dimensional data stores of unknown quality?  Tables should have a unique index.  Of some kind.  If it's a formal PK, or a unique constraint or a unique index, it needs have *something* unique enforced, or it's not really a relational table, but is instead a pile of unknown quality data. 



from sys.objects so

      left join sys.indexes si

            on si.object_id = so.object_id

            and si.is_unique = 1

where si.index_id is null

      and so.type_desc = 'user_table'

order by 1

 428 tables with no unique indexes.  This goes with the 36 completely duplicated indexes and 50+ partially duplicated indexes. 

And by the way, I checked.  is_primary_key implies is_unique, and is_unique_constraint implies is_unique.