SQL SERVER – How To Get List Of Primary Key & Foreign Key In Database?

SQL Server has some object for know the list of Primary Key and Foreign key in database.

There are Objects as follows-

1)      SYS.OBJECT

Example:

Use AdventureWorks

GO

SELECT

DISTINCT

Constraint_Name AS [NameOfConstraint],

Table_Schema AS [NameOfSchema],

Table_Name AS [NameOfTable]

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

GO

  

2)      INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Example:

Use AdventureWorks

GO

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,

SCHEMA_NAME(schema_id) AS NameofSchema,

OBJECT_NAME(parent_object_id) AS NameOfTable,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc IN ('PRIMARY_KEY_CONSTRAINT','FOREIGN_KEY_CONSTRAINT')

GO

3)      SYS.ALL_OBJECTS

Note: In this Method we cannot get the Table name but it will return the all Primary and Foreign Constraint of the Database.

Example:

Select

name as Constraintname,

type_desc as Tpyeofkey,

schema_id 

from sys.all_objects

where type_desc in('PRIMARY_KEY_CONSTRAINT','FOREIGN_KEY_CONSTRAINT')

Try It..