Primary Key for Table

All tables with Primary Key

SELECT 
   s.name as SchemaName, 
   t.name as TableName, 
   c.name as PKname
FROM 
	sys.key_constraints c 
	INNER JOIN sys.tables t ON c.parent_object_id = t.OBJECT_ID 
	INNER JOIN sys.schemas s ON c.schema_id = s.schema_id 
WHERE 
	c.type = 'PK'

All tables missing Primary Key

SELECT 
   s.name as SchemaName, 
   t.name as TableName
FROM 
	sys.tables t 
	INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 
WHERE 
	t.type = 'U' 
	AND 
	NOT EXISTS 
	(
		SELECT 
			*
        FROM 
			sys.key_constraints c 
        WHERE 
			c.parent_object_id = t.OBJECT_ID 
            AND 
			c.schema_id = s.schema_id 
            AND 
			c.type = 'PK' 
	)

• t.type – object type:
  • Table
  • View
• c.type – type of constraint:
  • Primary key
  • Unique key
  • Foreign key
  • Check constraint
  • Default constraint