一人アドカレ

日	ポスト
1	一時テーブルが存在していたら削除する
2	何度も使う中間テーブルに名前をつけてクエリを見やすくする
3	共通テーブル式と一時テーブルの参照可能な範囲の違い
4	グループ毎に最大値や最小値を持つレコードを抽出する
5
6	SQL Serverのデータインポートウィザードを使って日時データをインポートする
7	再帰共通テーブル式で連続した値を得る
8	拡張プロパティに入っているテーブルやカラムの説明を取得し表示する
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

拡張プロパティに入っているテーブルやカラムの説明を取得し表示する

課題

テーブルや列の拡張プロパティにそれらの説明があるとき、テーブル名・列名・それらの説明を列挙する。
これができるとテーブル群を理解する一助となる。

準備

実行環境

SQL Server 2017 Standard
SQL Server Management Studio 2017

サンプルデータ

sandbox というDB内に fruits テーブルがある。
fruits テーブルは以下のように定義している。

列名	データ型	説明
id	int	番号
name	varchar(10)	名前
price	int	値段

なお、 fruits テーブル自体にも「果物一覧」という説明を持たせている。

解法

USE [sandbox];
GO

SELECT Sys.objects.name AS [table_name]
     , ep1.value        AS [table_description]
     , Sys.columns.name AS [column_name] 
     , ep2.value        AS [column_description]
  FROM Sys.columns
 INNER JOIN Sys.objects
         ON Sys.objects.object_id = Sys.Columns.object_id
  -- テーブルの説明
  LEFT JOIN Sys.extended_properties ep1
         ON ep1.major_id = Sys.objects.object_id
            AND ep1.minor_id = 0
  -- カラムの説明
  LEFT JOIN Sys.extended_properties ep2
         ON ep2.major_id =  Sys.columns.object_id
            AND ep2.minor_id = Sys.columns.column_id
 WHERE Sys.objects.type_desc = 'USER_TABLE'
 ORDER BY Sys.objects.object_id
        , Sys.columns.column_id;

結果は次の通り

table_name	table_description	column_name	column_description
fruits	果物一覧	id	番号
fruits	果物一覧	name	名前
fruits	果物一覧	price	値段

Sys.objectsテーブルにはユーザーが定義したテーブルや制約などのオブジェクトの情報が記録されている。
Sys.columnsテーブルにはテーブルやビューなどの列の情報が記録されている。
Sys.extended_propertiesにはデータベース内の拡張プロパティについての情報が記録されている。
Sys.extended_properties.major_id, minor_idはテーブルや列のIDを示すのだが、どう示されるのかというとこちらのmajor_id, minor_idの説明にあるように、以下のような組み合わせになる。

オブジェクト	major_id	minor_id
table	object_id	0
column	object_id	column_id

以上のテーブルと条件を使って結合すると拡張プロパティの情報を抜き出せるので、拡張プロパティにテーブルや列の説明を入れておけば一覧で出力できる。

感想

テーブル定義書があればそこまで必要じゃないかもしれないが、残念ながら定義書が古かったり無かったりすることもままあるので、そういうときに使えると良いのかもしれない。
もしくはそういったときのことを考えて拡張プロパティに説明を入れておくのも手かもしれない。

参考