|
Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor related to Primary Keys and Foreign Keys in a database.
-- List all disabled foreign keys - QUICK SYNTAX
SELECT name AS DisabledForeignKey
FROM sys.foreign_keys
WHERE is_disabled=1
------------
-- Microsoft SQL Server T-SQL list all primary and foreign keys
-- mssql information_schema views - Database 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
*/
------------
-- Check FK-PK violations in entire database - check referential integrity
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;
GO
------------
Related articles:
Schema: How do I show all the primary keys in a database?
Composite Primary key and Foreign key
|
