システムテーブルを使おう

# はじめに
皆様、DB設計書は更新されていますでしょうか。
いつの間にか設計書と乖離が出ることは多々あります。
確認するにせよ1つ1つ見ていては時間がかかって仕方がありません。
そこで、システムテーブルから情報を取得して確認することで手間を減らすことが出来ます。

# クエリ
システム関数を使えば簡略化できますが、派生するデータを取得しやすくするためにテーブルを結合させています。

## データベース関連
テーブル、ビューなどを纏めて取得するクエリです。
「sys.objects」の「type」でオブジェクトの種類を絞り込めます。
```sql:情報
SELECT o.schema_id AS スキーマID
     , o.object_id AS ID
     , s.name AS スキーマ名
     , o.type_desc AS タイプ
     , o.name AS 名称
     , (SELECT e.name, e.value
          FROM sys.extended_properties AS e
         WHERE e.class = 1
           AND e.major_id = o.object_id
           AND e.minor_id = 0
           FOR XML PATH('parameter')
       ) AS 拡張パラメータ
     , CASE WHEN k.name IS NULL THEN 0 ELSE 1 END AS PK有無
     , i.last_value AS 自動採番最終値
     , p.rows AS データ件数
     , m.definition AS 構成クエリ
     , o.create_date AS 作成日時
     , o.modify_date AS 更新日時
  FROM sys.objects AS o
 INNER JOIN sys.schemas AS s
         ON s.schema_id = o.schema_id
  LEFT JOIN sys.key_constraints AS k
         ON k.parent_object_id = o.object_id
        AND k.type = 'PK'
  LEFT JOIN sys.partitions AS p
         ON p.object_id = o.object_id
        AND p.index_id <= 1
  LEFT JOIN sys.identity_columns AS i
         ON i.object_id = o.object_id
  LEFT JOIN sys.sql_modules AS m
         ON m.object_id = o.object_id
 WHERE o.type IN ('U', 'V')
 ORDER BY o.schema_id
        , o.object_id
```
```sql:カラム情報
SELECT o.schema_id AS スキーマID
     , o.object_id AS ID
     , c.column_id AS カラムID
     , o.type_desc AS タイプ
     , s.name AS スキーマ名
     , o.name AS 名称
     , c.name AS カラム名
     , (SELECT e.name, e.value
          FROM sys.extended_properties AS e
         WHERE e.class = 1
           AND e.major_id = c.object_id
           AND e.minor_id = c.column_id
           FOR XML PATH('parameter')
       ) AS 拡張パラメータ
     , t.name AS データ型
     , c.max_length AS 桁数
     , c.precision AS 有効桁数
     , c.scale AS 小数点桁数
     , c.is_nullable AS NULL許容
     , i.last_value AS 自動採番最終値
     , d.definition AS 初期値
  FROM sys.objects AS o
 INNER JOIN sys.schemas AS s
         ON s.schema_id = o.schema_id
  LEFT JOIN sys.columns AS c
         ON c.object_id = o.object_id
  LEFT JOIN sys.types AS t
         ON t.user_type_id = c.user_type_id
  LEFT JOIN sys.default_constraints AS d
         ON d.parent_object_id = o.object_id
        AND d.parent_column_id = c.column_id
  LEFT JOIN sys.identity_columns AS i
         ON i.object_id = c.object_id
        AND i.column_id = c.column_id
 WHERE o.type IN ('U', 'V')
 ORDER BY o.type
        , o.schema_id
        , o.object_id
        , c.column_id
```

テーブルごと、ビューごとに取得したい場合は以下のクエリになります。
<details><summary>テーブル情報</summary>

```sql:テーブル情報
SELECT o.schema_id AS スキーマID
     , o.object_id AS テーブルID
     , s.name AS スキーマ名
     , o.name AS テーブル名
     , (SELECT e.name, e.value
          FROM sys.extended_properties AS e
         WHERE e.class = 1
           AND e.major_id = o.object_id
           AND e.minor_id = 0
           FOR XML PATH('parameter')
       ) AS 拡張パラメータ
     , CASE WHEN k.name IS NULL THEN 0 ELSE 1 END AS PK有無
     , i.last_value AS 自動採番最終値
     , p.rows AS データ件数
     , o.create_date AS 作成日時
     , o.modify_date AS 更新日時
  FROM sys.tables AS o
 INNER JOIN sys.schemas AS s
         ON s.schema_id = o.schema_id
  LEFT JOIN sys.key_constraints AS k
         ON k.parent_object_id = o.object_id
        AND k.type = 'PK'
 INNER JOIN sys.partitions AS p
         ON p.object_id = o.object_id
        AND p.index_id <= 1
  LEFT JOIN sys.identity_columns AS i
         ON i.object_id = o.object_id
 ORDER BY o.schema_id
        , o.object_id
```
```sql:カラム情報
SELECT o.schema_id AS スキーマID
     , o.object_id AS テーブルID
     , c.column_id AS カラムID
     , s.name AS スキーマ名
     , o.name AS テーブル名
     , c.name AS カラム名
     , (SELECT e.name, e.value
          FROM sys.extended_properties AS e
         WHERE e.class = 1
           AND e.major_id = c.object_id
           AND e.minor_id = c.column_id
           FOR XML PATH('parameter')
       ) AS 拡張パラメータ
     , t.name AS データ型
     , c.max_length AS 桁数
     , c.scale AS 小数点桁数
     , c.is_nullable AS NULL許容
     , i.last_value AS 自動採番最終値
     , d.definition AS 初期値
  FROM sys.tables AS o
 INNER JOIN sys.schemas AS s
         ON s.schema_id = o.schema_id
 INNER JOIN sys.columns AS c
         ON c.object_id = o.object_id
 INNER JOIN sys.types AS t
         ON t.user_type_id = c.user_type_id
  LEFT JOIN sys.default_constraints AS d
         ON d.parent_object_id = o.object_id
        AND d.parent_column_id = c.column_id
  LEFT JOIN sys.identity_columns AS i
         ON i.object_id = c.object_id
        AND i.column_id = c.column_id
 ORDER BY o.type
        , o.schema_id
        , o.object_id
        , c.column_id
```
</details>

<details><summary>ビュー情報</summary>

```sql:ビュー情報
SELECT o.schema_id AS スキーマID
     , o.object_id AS ビューID
     , s.name AS スキーマ名
     , o.name AS ビュー名
     , m.definition AS 構成クエリ
     , (SELECT e.name, e.value
          FROM sys.extended_properties AS e
         WHERE e.class = 1
           AND e.major_id = o.object_id
           AND e.minor_id = 0
           FOR XML PATH('parameter')
       ) AS 拡張パラメータ
     , o.create_date AS 作成日時
     , o.modify_date AS 更新日時
  FROM sys.views AS o
 INNER JOIN sys.schemas AS s
         ON s.schema_id = o.schema_id
 INNER JOIN sys.sql_modules AS m
         ON m.object_id = o.object_id
 ORDER BY o.schema_id
        , o.object_id
```
```sql:カラム情報
SELECT o.schema_id AS スキーマID
     , o.object_id AS ビューID
     , c.column_id AS カラムID
     , s.name AS スキーマ名
     , o.name AS ビュー名
     , c.name AS カラム名
     , (SELECT e.name, e.value
          FROM sys.extended_properties AS e
         WHERE e.class = 1
           AND e.major_id = c.object_id
           AND e.minor_id = c.column_id
           FOR XML PATH('parameter')
       ) AS 拡張パラメータ
     , t.name AS データ型
     , c.max_length AS 桁数
     , c.scale AS 小数点桁数
     , c.is_nullable AS NULL許容
  FROM sys.views AS o
 INNER JOIN sys.schemas AS s
         ON s.schema_id = o.schema_id
 INNER JOIN sys.columns AS c
         ON c.object_id = o.object_id
 INNER JOIN sys.types AS t
         ON t.user_type_id = c.user_type_id
 ORDER BY o.type
        , o.schema_id
        , o.object_id
        , c.column_id
```
</details>

<details><summary>トリガー情報</summary>

```sql:トリガー情報
SELECT t.schema_id AS スキーマID
     , t.object_id AS テーブルID
     , o.object_id AS トリガーID
     , s.name AS スキーマ名
     , t.name AS テーブル名
     , o.name AS トリガー名
     , te.type_desc AS トリガー種類
     , m.definition AS 構成クエリ
     , o.create_date AS 作成日時
     , o.modify_date AS 更新日時
  FROM sys.triggers AS o
 INNER JOIN sys.trigger_events AS te
         ON te.object_id = o.object_id
 INNER JOIN sys.tables AS t
         ON t.object_id = o.parent_id
 INNER JOIN sys.schemas AS s
         ON s.schema_id = t.schema_id
 INNER JOIN sys.sql_modules AS m
         ON m.object_id = o.object_id
 ORDER BY t.schema_id
        , t.object_id
        , o.object_id
```
</details>

<details><summary>インデックス情報</summary>

```sql:インデックス情報
SELECT t.schema_id AS スキーマID
     , t.object_id AS テーブルID
     , i.index_id AS インデックスID
     , ic.index_column_id AS カラムID
     , s.name AS スキーマ名
     , t.name AS テーブル名
     , i.name AS インデックス名
     , c.name AS カラム名
     , i.type_desc AS タイプ
  FROM sys.indexes AS i
 INNER JOIN sys.tables AS t
         ON t.object_id = i.object_id
 INNER JOIN sys.schemas AS s
         ON s.schema_id = t.schema_id
 INNER JOIN sys.index_columns AS ic
         ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
 INNER JOIN sys.columns AS c
         ON c.object_id = i.object_id
        AND c.column_id = ic.index_column_id
 WHERE i.name IS NOT NULL
 ORDER BY t.schema_id
        , t.object_id
        , i.index_id
        , ic.index_column_id
```

</details>

## セキュリティ関連
```sql:ユーザー情報
SELECT dp.principal_id AS ユーザーID
     , dp.name AS ユーザー名
     , dp.type_desc AS 認証方法
     , dp.create_date AS 作成日時
     , dp.modify_date AS 更新日時
  FROM sys.database_principals AS dp
 WHERE dp.type IN ('S', 'U')
 ORDER BY dp.principal_id
```
```sql:ユーザー権限
SELECT dp.principal_id AS ユーザーID
     , o.object_id AS オブジェクトID
     , dp.name AS ユーザー名
     , o.name AS オブジェクト名
     , perm.permission_name AS 権限名
     , perm.state_desc AS 権限の状態
  FROM sys.database_principals AS dp
 INNER JOIN sys.database_permissions AS perm
         ON perm.grantee_principal_id = dp.principal_id
 INNER JOIN sys.objects AS o
         ON o.object_id = perm.major_id
 ORDER BY dp.principal_id
        , o.object_id
```