Finding Missing Primary Keys in SQL Server
Primary keys are unique identifier for each row of database table, as a good practice each table should have primary key defined. Here is a handy script which will run on SQL Server 2000 and SQL Server 2005 databases to get the list of tables with or with out primary key defined. It uses sysobjects table in 2000 and sysobjects view in 2005. There are other easy ways of doing it specific to each database but i personally like scripts which can run on both versions.
Tables with out or missing primary key.
SELECT
USER_NAME(ObjectProperty(id,'OwnerID')) AS Owner
, [name] AS Table_Name
FROM sysobjects
WHERE OBJECTPROPERTY(id,'TableHasPrimaryKey') = 0
AND
OBJECTPROPERTY(id,'IsMSShipped') = 0
AND [type] = 'U'
ORDER BY [name]
List of tables with primary key defined.
SELECT
USER_NAME(ObjectProperty(id,'OwnerID')) AS Owner
, [name] AS Table_Name
FROM sysobjects
WHERE OBJECTPROPERTY(id,'TableHasPrimaryKey') = 1
AND
OBJECTPROPERTY(id,'IsMSShipped') = 0
AND [type] = 'U'
ORDER BY [name]
The above script is using OBJECTPROPERTY TableHasPrimaryKey property to check if table has primary key or not, it also checks if the object is shipped by Microsoft. You do not have to use [type] = 'U' because it will skip stored procedures and other objects automatically as they can not have Primary Key(s) defined, but i have put it in my T-SQL script to make the script clear.