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

Last modified: February 28, 2020

Author

Comments

Write a Reply or Comment