一人アドカレ
| 日 | ポスト |
|---|---|
| 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 |
以上のテーブルと条件を使って結合すると拡張プロパティの情報を抜き出せるので、拡張プロパティにテーブルや列の説明を入れておけば一覧で出力できる。
感想
テーブル定義書があればそこまで必要じゃないかもしれないが、残念ながら定義書が古かったり無かったりすることもままあるので、そういうときに使えると良いのかもしれない。
もしくはそういったときのことを考えて拡張プロパティに説明を入れておくのも手かもしれない。