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
-
-
- GO
<< SQL Server: System Views