Post
Ask Question

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.

  1. CREATE DATABASE DB_INFORMATION_SCHEMA_VIEW  
  2. GO  
  3.   
  4. USE DB_INFORMATION_SCHEMA_VIEW  
  5. GO  
  6.   
  7. CREATE TABLE tbl_parent  
  8. (  
  9.     Id INT IDENTITY(1,1) CONSTRAINT PK_tbl_parent_Id PRIMARY KEY,  
  10.     Name VARCHAR(50)  
  11. )  
  12. GO  
  13.   
  14. CREATE TABLE tbl_child  
  15. (     
  16.     Id INT IDENTITY(1,1) PRIMARY KEY,  
  17.     Name VARCHAR(50),  
  18.     ParentId INT CONSTRAINT FK_tbl_parent_tbl_child_ParentId FOREIGN KEY REFERENCES tbl_parent(Id)  
  19. )  
  20. 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.
  1. USE DB_INFORMATION_SCHEMA_VIEW  
  2. GO  
  3.   
  4. SELECT * FROM information_schema.key_column_usage  
  5. --WHERE table_name = 'tbl_child'  
  6.   
  7. GO  
query output

<< SQL Server: System Views