初級者向けSQL Serverのsysビュー入門

SQL Serverには、データベース内部の情報を参照するための「システムビュー」が用意されています。

これらはすべて sys スキーマ配下にあり、テーブル/ビュー/カラムといったオブジェクト情報や、データベース設定、ユーザー情報などを確認できます。

本記事では、初心者向けに代表的な sys ビューの使い方を解説し、最後に演習問題と解答例を紹介します。


sysビューとは?

sys スキーマ

SQL Serverのシステムオブジェクトはすべて sys スキーマ下に存在します。たとえば、テーブルの一覧を取得するには sys.tables、カラム情報は sys.columns です。

主な特徴
  • 動的管理ビュー(Dynamic Management Views, DMV)とは異なり、常に最新のメタデータを参照可能
  • バージョン間で互換性が高い(SQL Server 2005以降)
  • セキュリティ権限が必要(データベースレベルで「ビュー定義」権限など)

よく使うsysビュー

sys.objects

すべてのオブジェクト(テーブル・ビュー・ストアドプロシージャ・関数など)を一覧化します。

SELECT 
    object_id,
    name,
    type_desc,
    create_date
FROM sys.objects
WHERE type IN ('U', 'V')  -- U: ユーザーテーブル、V: ビュー
ORDER BY create_date DESC;
  • object_id: オブジェクト固有ID
  • name: オブジェクト名
  • type_desc: オブジェクト種別の説明
  • create_date: 作成日

sys.tables

ユーザー定義テーブルのみを取得します。

SELECT 
    object_id,
    name AS table_name,
    schema_id
FROM sys.tables
ORDER BY name;

schema_id を sys.schemas と結合するとスキーマ名もわかります。

SELECT
    t.name AS table_name,
    s.name AS schema_name
FROM sys.tables t
JOIN sys.schemas s
  ON t.schema_id = s.schema_id;

sys.columns

テーブル/ビューのカラム情報を取得します。

SELECT
    c.object_id,
    o.name AS object_name,
    c.name AS column_name,
    c.max_length,
    c.is_nullable
FROM sys.columns c
JOIN sys.objects o
  ON c.object_id = o.object_id
WHERE o.name = 'YourTableName'
ORDER BY c.column_id;
  • max_length: データ長(文字数やバイト数)
  • is_nullable: NULL許可フラグ

sys.databases

サーバー上のデータベース一覧を表示します。

SELECT
    database_id,
    name,
    state_desc,
    recovery_model_desc
FROM sys.databases;
  • state_desc: ONLINE/OFFLINE など
  • recovery_model_desc: リカバリモデル(FULL, SIMPLE, BULK_LOGGED)

sys.schemas

スキーマ(所有者別名前空間)の一覧を表示します。

SELECT
    schema_id,
    name AS schema_name,
    principal_id
FROM sys.schemas;

principal_id: スキーマ所有者のID(sys.database_principals と結合可能)


sysビュー活用のポイント

  1. JOINで関連情報を取得
    多くの sys ビューは object_id や schema_id などで結合できます。
  2. フィルタリング
    WHERE 句で type/is_ms_shipped(システムオブジェクト除外)を指定すると、必要な情報に絞り込みやすい。
  3. 権限管理
    参照権限がないとエラーになるため、DBA権限または「ビュー定義(VIEW DEFINITION)」権限を付与してもらいましょう。

まとめ

  • sys スキーマ配下のビューは、SQL Serverのメタデータ参照に必須
  • sys.objects, sys.tables, sys.columns, sys.databases, sys.schemas が特によく使われる
  • JOINやフィルタを活用して、必要な情報を素早く取得できる

演習問題

以下の演習問題にチャレンジしてみましょう。

問題1

現在のデータベース内に存在するすべてのユーザーテーブル名とその作成日を取得せよ。結果は作成日の降順で並べ替えること。

問題2

スキーマ名ごとに含まれるテーブル数を集計せよ。スキーマ名とテーブル数を表示し、テーブル数の多い順に並べ替えること。

問題3

特定のテーブル(例:Employees)の各カラム名とデータ型、NULL許可フラグを一覧表示せよ。


解答例

解答1

SELECT
    t.name AS table_name,
    t.create_date
FROM sys.tables t
ORDER BY t.create_date DESC;

解答2

SELECT
    s.name AS schema_name,
    COUNT(*) AS table_count
FROM sys.tables t
JOIN sys.schemas s
  ON t.schema_id = s.schema_id
GROUP BY s.name
ORDER BY COUNT(*) DESC;

解答3

SELECT
    c.name AS column_name,
    TYPE_NAME(c.user_type_id) AS data_type,
    c.is_nullable
FROM sys.columns c
JOIN sys.tables t
  ON c.object_id = t.object_id
WHERE t.name = 'Employees'
ORDER BY c.column_id;

以上で、初級者向け「SQL Serverのsysビュー入門」を終わります。この記事をもとに sys ビューを使いこなし、データベースのメタデータ参照をマスターしましょう!