You are here:
Adobe Apollo Adobe Apollo

Microsoft SQL 2005 find tables missing primary keys

I recently setup replication for Microsoft SQL 2005 server for a client and needed to provide a list of tables to the client without primary keys, as these are not transferred in the replication. This script came in very handy so thought I would share.

SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = ‘U’
AND NOT EXISTS

(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = ‘PK’ )

Hope this helps!