List all User Tables With and Without Primary Key Constraint in a SQL Server Database

To list all user tables in a SQL Server database that have a Primary Key constraint, you can use the following query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.is_primary_key = 1
AND o.type = 'U'
ORDER BY o.name, i.type

There can only be one primary key in a table, but it is possible to have a composite primary key - a primary key made from two or more columns.

To find all tables WITHOUT Primary Key Constraint, use this query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, t.name AS Table_Name
FROM sys.tables t
INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE OBJECTPROPERTY(t.object_id,'TableHasPrimaryKey') = 0
AND t.type = 'U'
ORDER BY t.name