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

  1. CREATE DATABASE DB_INFORMATION_SCHEMA_VIEW
  2. GO
  4. USE DB_INFORMATION_SCHEMA_VIEW
  5. GO
  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
  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
  4. SELECT * FROM information_schema.key_column_usage
  5. --WHERE table_name = 'tbl_child'
  7. GO
query output

<< SQL Server: System Views