DBに論理名をつける

はじめに

DB設計書にはテーブルやカラムの論理名や備考が書かれていると思います。
DBを構築する際に論理名や備考を設定しておくことができます。
使い道があるかどうかは不明ですが、DBから設計書を作成するときには少し役立ちます。

どこの設定するかというと「拡張プロパティ」に設定します。

SSMSではプロパティ画面で設定/確認ができますが、いちいち開くのが面倒なのでクエリで行う方法を紹介します。

クエリ

登録/更新/削除

-- 登録：sp_addextendedproperty
-- 更新：sp_updateextendedproperty
-- 削除：sp_dropextendedproperty
EXEC sys.sp_addextendedproperty
-- 設定値の名前
     @name = 'MS_Description'
-- スキーマ/テーブル/カラムの設定の時に必須
   , @level0type = 'SCHEMA'
   , @level0name = '{スキーマの物理名}'
-- テーブル/カラムの設定の時に必須
   , @level1type = 'TABLE'
   , @level1name = '{テーブルの物理名}'
-- カラムの設定の時に必須
   , @level2type = 'COLUMN'
   , @level2name = '{カラムの物理名}'
-- 設定値（削除の時は不要）
   , @value = '設定値'

同じカラム（スキーマ/テーブル）に複数のプロパティを設定する場合は「name」の値を変えます。
SSMSで登録する場合は以下のようにします。

読出

SELECT * FROM sys.extended_properties

class	class_desc	major_id	minor_id	name	value
1	OBJECT_OR_COLUMN	658101385	0	description	各科目のテスト結果を登録
1	OBJECT_OR_COLUMN	658101385	0	logical_name	点数マスタ
1	OBJECT_OR_COLUMN	658101385	1	MS_Description	学籍番号
1	OBJECT_OR_COLUMN	658101385	2	MS_Description	科目
1	OBJECT_OR_COLUMN	658101385	3	MS_Description	点数
3	SCHEMA	1	0	MS_Description	デフォルトのスキーマ

システムテーブルと結合する場合は
major_id：「sys.tables」の「object_id」（スキーマの場合は「1」）
minor_id：「sys.columns」の「column_id」（テーブルの場合は「0」）