Finding tables with no primary or alternate keys

I was fiddling about in a database working on a few things when I noticed that there was duplicated data in a column that I did not expect to have duplication in.  I went to my ERwin model and sure enough, no alternate key was defined on the column in question.  It was only a test database, but still, what a mess that would be if I had forgotten to put this key on in the production version.  To that end I wrote a couple of queries to check for objects that did not have a primary key, or they have a primary key that is a surrogate identity value but have no UNIQUE constraints assigned. 

Very important stuff because two rows whose only difference is the UNIQUE constraints are pretty much the same data with two pointers to them.  There are cases where this is not a problem, like perhaps a logging table, but certainly every table in your logical model needs a non-surrogate key defined, and then implemented in the implementation model.

select ' the following tables have no primary key defined'

select distinct cast(table_schema + '.' +  table_name as varchar(100)) as tableName
from   information_schema.tables
where  objectproperty(object_id(table_schema + '.' + table_name),'isMsShipped') = 0
  and  table_type = 'base table'
  and  table_name not like 'dt%'
  and  table_schema + '.' +  table_name not in
  (select table_schema + '.' + table_name
   from   information_schema.table_constraints
   where  objectproperty(object_id(constraint_schema + '.' + constraint_name),'isPrimaryKey') = 1)

select ' the following tables have identity based primary keys but no alternate key constraints'

select distinct cast(table_schema + '.' +  table_name as varchar(100)) as tableName
from   information_schema.key_column_usage
where  objectproperty(object_id(constraint_schema + '.' + constraint_name),'isPrimaryKey') = 1
  and  objectproperty(object_id(table_schema + '.' + table_name),'isMsShipped') = 0
  and  columnproperty(object_id(table_schema + '.' + table_name),column_name,'IsIdentity') = 1
  and  table_name not like 'dt%'
  and  table_schema + '.' +  table_name
 not in (select distinct table_schema + '.'+ table_name
  from   information_schema.key_column_usage
  where  objectproperty(object_id(constraint_schema + '.' + constraint_name),'isUniqueCnst') = 1
    and  objectproperty(object_id(table_schema + '.' + table_name),'isMsShipped') = 0
    and  columnproperty(object_id(table_schema + '.' + table_name),column_name,'IsIdentity') = 0
    and  table_name not like 'dt%'
  )