|
Execute the following T-SQL example script in Microsoft SQL Server Management Studio Query Editor 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 AdventureWorks2008; 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 Sales SalesOrderHeader ContactID FOREIGN KEY Sales SalesOrderHeader CreditCardID FOREIGN KEY Sales SalesOrderHeader CurrencyRateID FOREIGN KEY Sales SalesOrderHeader CustomerID FOREIGN KEY Sales SalesOrderHeader SalesOrderID PRIMARY KEY Sales SalesOrderHeader SalesPersonID FOREIGN KEY
*/ ------------
|