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.


select s.name+'.'+ t.name
from
  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 i.name is null
order by (s.name+'.'+ t.name) asc;

No comments: