SQL Server: Find Foreign Keys and Primary Key in a Table

SQL server creates “INFORMATION_SCHEMA“ views for retrieviing metadata about the objects within a database.

CREATE DATABASE DB_INFORMATION_SCHEMA_VIEW
GO

USE DB_INFORMATION_SCHEMA_VIEW
GO

CREATE TABLE tbl_parent
(
    Id INT IDENTITY(1,1) CONSTRAINT PK_tbl_parent_Id PRIMARY KEY,
    Name VARCHAR(50)
)
GO

CREATE TABLE tbl_child
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    ParentId INT CONSTRAINT FK_tbl_parent_tbl_child_ParentId FOREIGN KEY REFERENCES tbl_parent(Id)
)
GO

If we want to know the table’s primary keys and foreign keys.
 
We can simply use an “information_schema.key_column_usage” view, this view will return all of the table's foreign keys and primary keys.

USE DB_INFORMATION_SCHEMA_VIEW
GO

SELECT * FROM information_schema.key_column_usage
--WHERE table_name = 'tbl_child'

GO

<< SQL Server: System Views