SQL Server Helper - Tip of the Day

To determine if a table has a primary key, run the following statement:

SELECT OBJECTPROPERTY(OBJECT_ID(N'[dbo].[User]'), 
                     'TableHasPrimaryKey')

A value of 1 means that the specified table has a Primary Key constraint while a value of 0 means the table doesn’t have a Primary Key constraint.  A value of NULL means either the table doesn’t exist or the object name passed is not a table.

To get a list of tables that doesn’t have a Primary Key constraint, run the following statement:

SELECT [Name]
FROM [sys].[objects]
WHERE [Type_Desc] = 'USER_TABLE' AND
      OBJECTPROPERTY([Object_ID], 'TableHasPrimaryKey') = 0
ORDER BY [Name]