SSMS、T-SQL、システムビューを使って主キーを簡単抽出
SQL Serverテーブルの主キーは、テーブル内の各レコードを一意に識別する列です。主キーは、データの整合性と一貫性を保つために不可欠な要素です。
この解説では、以下の3つの方法でSQL Serverテーブルの主キーを一覧表示する方法を説明します。
- sys.indexes システムビューを使用する
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE ビューを使用する
- sp_helpindex システムストアドプロシージャを使用する
手順
- SQL Server Management Studio (SSMS) を起動し、対象のデータベースに接続します。
- オブジェクトエクスプローラーで、データベース > テーブル > 対象のテーブルを展開します。
- sys.indexes システムビューを右クリックし、新しいクエリ を選択します。
- 以下のクエリを実行します。
SELECT name
FROM sys.indexes
WHERE is_primary_key = 1
AND object_id = OBJECT_ID(N'テーブル名');
解説
sys.indexes
システムビューは、データベース内のすべてのインデックスに関する情報を提供します。is_primary_key
列は、インデックスが主キーかどうかを示します。object_id
関数は、テーブルの名前をIDに変換します。
例
USE AdventureWorks2019;
SELECT name
FROM sys.indexes
WHERE is_primary_key = 1
AND object_id = OBJECT_ID(N'Person');
結果
PK_Person_PersonID
- SSMS を起動し、対象のデータベースに接続します。
- オブジェクトエクスプローラーで、データベース > ビュー > INFORMATION_SCHEMA > KEY_COLUMN_USAGE ビューを展開します。
SELECT table_name, column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name = 'PK_テーブル名';
constraint_name
列は、主キーまたは外部キーの名前を示します。
USE AdventureWorks2019;
SELECT table_name, column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name = 'PK_Person';
Person
PersonID
EXEC sp_helpindex @objname = N'テーブル名';
sp_helpindex
システムストアドプロシージャは、指定されたテーブルのインデックスに関する情報を提供します。
USE AdventureWorks2019;
EXEC sp_helpindex @objname = N'Person';
...
Index Name: PK_Person_PersonID
...
補足
- 上記の3つの方法は、いずれも単一のテーブルの主キーを一覧表示する方法です。複数のテーブルの主キーを一覧表示したい場合は、
WHERE
句を変更する必要があります。 - 主キーは、テーブルに1つだけ存在する必要があります。
USE AdventureWorks2019;
SELECT name
FROM sys.indexes
WHERE is_primary_key = 1
AND object_id = OBJECT_ID(N'Person');
PK_Person_PersonID
USE AdventureWorks2019;
SELECT table_name, column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name = 'PK_Person';
Person
PersonID
USE AdventureWorks2019;
EXEC sp_helpindex @objname = N'Person';
...
Index Name: PK_Person_PersonID
...
- 上記のサンプルコードは、AdventureWorks2019 データベースの
Person
テーブルを使用しています。 - 他のテーブルを使用する場合は、テーブル名を変更する必要があります。
SQL Serverテーブルの主キーを一覧表示するその他の方法
SSMSのオブジェクトエクスプローラーを使用する
- テーブルのプロパティを開きます。
- インデックス ページを選択します。
- 主キー 列に表示されているインデックス名が主キーです。
SELECT INTO 構文を使用する
SELECT *
INTO NewTable
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name = 'PK_テーブル名';
- このクエリは、
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ビューから主キーに関する情報を抽出し、NewTable
という新しいテーブルに保存します。 NewTable
テーブルには、主キーに関する以下の情報が含まれます。TABLE_NAME
: テーブル名COLUMN_NAME
: 主キー列名
T-SQL スクリプトを使用する
以下のT-SQLスクリプトは、データベース内のすべてのテーブルの主キーを一覧表示します。
DECLARE @TableName VARCHAR(128)
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT name
FROM sys.indexes
WHERE is_primary_key = 1
AND object_id = OBJECT_ID(@TableName)
FETCH NEXT FROM @Cursor INTO @TableName
END
CLOSE @Cursor
DEALLOCATE @Cursor
- このスクリプトは、
INFORMATION_SCHEMA.TABLES
ビューを使用して、データベース内のすべてのテーブルの名前を取得します。 - 次に、
sys.indexes
システムビューを使用して、各テーブルの主キーの名前を取得します。
sql sql-server t-sql