SQL SERVER -Get or Find or Retrieve the list all Primary Key, Foreign Key and Unique Key into the Database

In this article I am going to explain How to get or find or retrieve the list all Primary Key, Foreign Key and Unique Key into the database.

There is Two difference method to get/find/retrieve the list all Primary Key, Foreign Key and Unique Key into the database.

Retrive the List of Primary Key using sys.objects: 

-----------------------Get all PRIMARY key into the Database-----------------------------------------

SELECT 
OBJECT_NAME(OBJECT_ID) AS 'PrimaryKeyName',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT'

Retrive the List of Foreign Key using sys.objects: 

-------------------------Get all FOREIGN key into the Database--------------------------------------

SELECT 
OBJECT_NAME(OBJECT_ID) AS 'PrimaryKeyName',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT'

Retrive the List of Unique Key using sys.objects: 

-------------------------Get all UNIQUE key into the Database--------------------------------------

SELECT 
OBJECT_NAME(OBJECT_ID) AS 'PrimaryKeyName',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'UNIQUE_CONSTRAINT'

Retrive the List of Primary/Foreign/Unique Key using INFORMATION_SCHEMA.KEY_COLUMN_USAGE View: 

-------Get all Primary Key, UniqueKay and FOREIGN Key into the database using view---------

SELECT [CONSTRAINT_CATALOG] AS 'DatabaseName'
      ,[CONSTRAINT_SCHEMA] AS 'KeySchemaName'
      ,[CONSTRAINT_NAME] AS 'KeyName'
      ,[TABLE_NAME] AS 'TableName'
      ,[COLUMN_NAME] AS 'ColumnName'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE