Transact Sql

SQL Server のシステム オブジェクトを使用しているため、他の RDB システムでは使用できないので注意。

データベース内にある各テーブルの構造を一覧で取得する

用途としては、既存のデータベースからテーブル構造を仕様書として落とし込みしたい時などに使う。
Management Studio などで下記のクエリを実行して、結果をエクセルなどにコピペするだけの簡単作業。

SET NOCOUNT ON

SELECT o.name AS テーブル名,
    c.column_id AS [No],
    CASE WHEN pk.column_id IS NOT NULL THEN '○' ELSE '' END AS PK,
    c.name AS 列名,
    type_name(c.user_type_id) AS データ型,
    CASE 
        -- Unicode系の文字列型は、サイズが文字数の2倍
        WHEN type_name(c.user_type_id) IN ('nvarchar', 'nchar') THEN cast(c.max_length / 2 AS VARCHAR)
        -- 小数ありの真数型は小数部も表示
        WHEN type_name(c.user_type_id) IN ('numeric', 'money') THEN cast(c.precision AS VARCHAR) + ',' + cast(c.scale AS VARCHAR) ELSE cast(c.max_length AS VARCHAR)
    END AS 長さ,
    CASE WHEN c.is_nullable = 0 THEN '×' ELSE '○' END AS null許容,
    CASE WHEN ep.value IS NULL THEN '' ELSE ep.value END AS 説明
FROM sys.columns AS c
INNER JOIN sys.objects AS o ON c.object_id = o.object_id
LEFT OUTER JOIN (
    -- 主キーのインデックスを抽出
    SELECT ic.object_id, ic.column_id
    FROM sys.index_columns AS ic
    INNER JOIN sys.indexes AS i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.is_primary_key = 1
) AS pk ON c.object_id = pk.object_id AND c.column_id = pk.column_id
LEFT OUTER JOIN (
    -- 列の説明を拡張プロパティから取得
    SELECT major_id, minor_id, value
    FROM sys.extended_properties
    WHERE minor_id <> 0 AND name = 'MS_Description'
) AS ep ON c.object_id = ep.major_id AND c.column_id = ep.minor_id
WHERE o.type = 'U' AND o.name LIKE '%' -- テーブル名で絞り込み
--WHERE o.type = 'V' AND o.name like '%' -- ビュー名で絞り込み
ORDER BY テーブル名, [No]