Tuesday 3 June 2008

Evil Tables without PRIMARY KEYS

Can't be replicated. Here's how to find them....

SQL 2005+
SELECT SCHEMA_NAME(schema_id) ,name
FROM sys.tables 
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY 1, 2

SQL 2000
SELECT c.name, a.name
FROM sysobjects a
INNER JOIN sysusers c ON a.uid = c.uid
LEFT OUTER JOIN (SELECT id FROM sysindexes WHERE (status & 2048)<>0 )b on a.id = b.id
WHERE xtype = 'U'
AND b.id IS NULL

No comments: