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]