Listing all the Primary Keys and Foreign Keys in a Database
Execute the following script in Microsoft SQL Server Management Studio Query Analyzer
to list all the primary keys and foreign keys in a database.
— Microsoft SQL Server T-SQL list all primary and foreign keys
— mssql information_schema views
— Database table primary key and foreign key columns
USE AdventureWorksDW;
SELECT SchemaName=c.table_schema,
TableName=c.table_name,
ColumnName=column_name,
KeyConstraint=constraint_type
FROM information_schema.table_constraints pk
INNER JOIN information_schema.key_column_usage c
ON c.table_name = pk.table_name
AND c.constraint_name = pk.constraint_name
–WHERE c.table_schema = ‘Sales’ — uncomment it for a specific schema
ORDER BY SchemaName, TableName, ColumnName
GO
/* Partial results
SchemaName TableName ColumnName KeyConstraint
Sales SalesOrderDetail ProductID FOREIGN KEY
Sales SalesOrderDetail SalesOrderDetailID PRIMARY KEY
Sales SalesOrderDetail SalesOrderID PRIMARY KEY
Sales SalesOrderDetail SalesOrderID FOREIGN KEY
Sales SalesOrderDetail SpecialOfferID FOREIGN KEY
Sales SalesOrderHeader BillToAddressID FOREIGN KEY
********************************************************************************************************