Wednesday, November 24, 2010

T-SQL Query: Tables Without a Primary Keys

I used this when I was setting up a Transactional Replication system on SQL Server. I needed to figure out which tables did not have primary keys assigned.

  sys.schemas s
  join sys.tables t on s.schema_id = t.schema_id
  left join sys.indexes i on i.object_id = t.object_id and
                             i.is_primary_key = 1
where is null
order by ('.'+ asc;