はじめに
皆様、DB設計書は更新されていますでしょうか。
いつの間にか設計書と乖離が出ることは多々あります。
確認するにせよ1つ1つ見ていては時間がかかって仕方がありません。
そこで、システムテーブルから情報を取得して確認することで手間を減らすことが出来ます。
クエリ
システム関数を使えば簡略化できますが、派生するデータを取得しやすくするためにテーブルを結合させています。
データベース関連
テーブル、ビューなどを纏めて取得するクエリです。
「sys.objects」の「type」でオブジェクトの種類を絞り込めます。
情報
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
カラム情報
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.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
テーブルごと、ビューごとに取得したい場合は以下のクエリになります。
テーブル情報
テーブル情報
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
カラム情報
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
ビュー情報
ビュー情報
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
カラム情報
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
トリガー情報
トリガー情報
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
インデックス情報
インデックス情報
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
セキュリティ関連
ユーザー情報
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
ユーザー権限
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