T-SQL と SSMS を使用して SQL Server で外部キーを簡単に見つける
SQL Server でデータベース内のすべての外部キーをリストする方法
SQL Server Management Studio を使用する
- オブジェクト エクスプローラーで、外部キーを表示したいデータベースを展開します。
- テーブルを右クリックし、 [デザイン] を選択します。
- [テーブル デザイナー] ウィンドウが開きます。
- [関係] タブを選択します。
- このタブには、選択したテーブルが参照する外部キーと、参照されるテーブルの情報が表示されます。
Transact-SQL を使用する
以下のクエリを使用して、データベース内のすべての外部キーをリストできます。
SELECT
t.name AS TableName,
c.name AS ConstraintName,
kc.col_name AS ReferencedColumnName,
rc.name AS ReferencedTableName
FROM
sys.tables t
INNER JOIN
sys.foreign_keys c
ON
t.object_id = c.parent_object_id
INNER JOIN
sys.key_columns kc
ON
c.id = kc.constraint_id
INNER JOIN
sys.tables rc
ON
kc.referenced_object_id = rc.object_id
このクエリは、以下の列を返します。
- TableName: 外部キーを持つテーブルの名前
- ConstraintName: 外部キー制約の名前
- ReferencedColumnName: 参照される列の名前
補足
- 上記のクエリは、現在のデータベースのみを対象としています。他のデータベースの外部キーをリストするには、USE ステートメントを使用してデータベースを切り替える必要があります。
USE AdventureWorks;
SELECT
t.name AS TableName,
c.name AS ConstraintName,
kc.col_name AS ReferencedColumnName,
rc.name AS ReferencedTableName
FROM
sys.tables t
INNER JOIN
sys.foreign_keys c
ON
t.object_id = c.parent_object_id
INNER JOIN
sys.key_columns kc
ON
c.id = kc.constraint_id
INNER JOIN
sys.tables rc
ON
kc.referenced_object_id = rc.object_id;
このコードを実行すると、次のような結果が表示されます。
TableName ConstraintName ReferencedColumnName ReferencedTableName
-------------- ---------------------- ---------------------- -----------------------------
SalesOrderHeader FK_SalesOrderHeader_BillToCustomerID CustomerID Customer
SalesOrderHeader FK_SalesOrderHeader_ShipToCustomerID CustomerID Customer
SalesOrderDetail FK_SalesOrderDetail_SalesOrderID SalesOrderID SalesOrderHeader
PurchaseOrderHeader FK_PurchaseOrderHeader_VendorID VendorID Vendor
PurchaseOrderDetail FK_PurchaseOrderDetail_PurchaseOrderID PurchaseOrderID PurchaseOrderHeader
この結果は、SalesOrderHeader
テーブルには BillToCustomerID
と ShipToCustomerID
という2つの外部キーがあり、どちらも Customer
テーブルの CustomerID
列を参照していることを示しています。また、SalesOrderDetail
テーブルには SalesOrderID
という外部キーがあり、SalesOrderHeader
テーブルの SalesOrderID
列を参照していることも示しています。
このコードをカスタマイズして、特定のデータベースやテーブルの外部キーのみをリストすることもできます。
SQL Server で外部キーをリストするその他の方法
上記に加えて、以下の方法でも外部キーをリストできます。
PowerShell を使用して、SQL Server インスタンスに接続し、外部キーに関する情報を取得できます。以下の例では、AdventureWorks
データベース内のすべての外部キーをリストする方法を示します。
Import-Module SqlServer
$connection = New-Object System.Data.SqlClient.SqlConnection("server=localhost;database=AdventureWorks;Integrated Security=SSPI")
$connection.Open()
$query = "SELECT t.name AS TableName, c.name AS ConstraintName, kc.col_name AS ReferencedColumnName, rc.name AS ReferencedTableName FROM sys.tables t INNER JOIN sys.foreign_keys c ON t.object_id = c.parent_object_id INNER JOIN sys.key_columns kc ON c.id = kc.constraint_id INNER JOIN sys.tables rc ON kc.referenced_object_id = rc.object_id"
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
$reader = $command.ExecuteReader()
while ($reader.Read()) {
Write-Output ("TableName: $($reader["TableName"])")
Write-Output ("ConstraintName: $($reader["ConstraintName"])")
Write-Output ("ReferencedColumnName: $($reader["ReferencedColumnName"])")
Write-Output ("ReferencedTableName: $($reader["ReferencedTableName"])")
Write-Output ("----")
}
$reader.Close()
$connection.Close()
データベース管理ツールを使用する
いくつかのサードパーティ製データベース管理ツールでは、GUI を使用して外部キーを簡単にリストできます。これらのツールの中には、視覚的にわかりやすい方法で外部キーを表示したり、外部キーに関する詳細情報を提供したりするものがあります。
スクリプトを使用する
インターネット上には、SQL Server で外部キーをリストするためのさまざまなスクリプトが公開されています。これらのスクリプトは、特定のニーズに合わせてカスタマイズしたり、追加機能を追加したりすることができます。
使用する方法は、ニーズと好みによって異なります。
- SSMS は、外部キーをリストする最も簡単な方法ですが、高度な機能は備わっていません。
- T-SQL は、より多くの柔軟性と制御性を提供しますが、SSMS よりも習得に時間がかかります。
- PowerShell は、PowerShell に慣れているユーザーにとって便利なオプションです。
- データベース管理ツール は、GUI を使用して外部キーを簡単にリストしたいユーザーにとって良いオプションです。
- スクリプト は、特定のニーズに合わせてカスタマイズする必要がある場合に役立ちます。
SQL Server で外部キーをリストするには、さまざまな方法があります。自分に合った方法を選択して、データベースの外部キー関係を理解してください。
sql-server