SQL Server: データベースの列名を素早く把握！管理ビューとシステムカタログビューを使いこなす

SQL Server のすべてのデータベースのすべてのテーブルの列名を検索する方法

このチュートリアルでは、SQL Server のすべてのデータベースのすべてのテーブルの列名を T-SQL を使用して取得する方法を説明します。 2 つの方法を紹介します。

1. 動的管理ビュー (DMV) を使用する
2. システム カタログ ビューを使用する

動的管理ビュー (DMV) は、SQL Server インスタンスに関するリアルタイム情報を提供するビューです。 列名を検索するには、次の DMV を使用できます。

• sys.columns: この DMV には、すべてのユーザー テーブルの列に関する情報が含まれています。

次のクエリは、sys.columns DMV を使用してすべてのデータベースのすべてのテーブルの列名を検索します。

SELECT
    d.name AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.data_type AS DataType
FROM sys.databases d
JOIN sys.schemas s ON d.database_id = s.database_id
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id

システム カタログ ビューは、SQL Server インスタンスのスキーマに関する情報を提供するビューです。 列名を検索するには、次のシステム カタログ ビューを使用できます。

• INFORMATION_SCHEMA.COLUMNS: このビューには、すべてのユーザー テーブルの列に関する情報が含まれています。

次のクエリは、INFORMATION_SCHEMA.COLUMNS ビューを使用してすべてのデータベースのすべてのテーブルの列名を検索します。

SELECT
    d.name AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.data_type AS DataType
FROM INFORMATION_SCHEMA.DATABASES d
JOIN INFORMATION_SCHEMA.SCHEMAS s ON d.name = s.DATABASE_NAME
JOIN INFORMATION_SCHEMA.TABLES t ON s.SCHEMA_NAME = t.TABLE_SCHEMA
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME

説明

上記のクエリは、次のことを行います。

1. sys.databases または INFORMATION_SCHEMA.DATABASES テーブルからすべてのデータベースをループします。
2. 各データベースに対して、sys.schemas または INFORMATION_SCHEMA.SCHEMAS テーブルからそのデータベースのすべてのスキーマをループします。
3. 各スキーマに対して、sys.tables または INFORMATION_SCHEMA.TABLES テーブルからそのスキーマのすべてのテーブルをループします。
4. 各テーブルに対して、sys.columns または INFORMATION_SCHEMA.COLUMNS テーブルからそのテーブルのすべての列を取得します。

• より高速なパフォーマンスが必要な場合は、sys.dm_db_partition_stats DMV を使用して、各データベースのパーティションごとに列名を検索できます。
• クエリを実行する前に、必要な権限を持っていることを確認してください。

SELECT
    d.name AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.data_type AS DataType
FROM sys.databases d
JOIN sys.schemas s ON d.database_id = s.database_id
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id

SELECT
    d.name AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.data_type AS DataType
FROM INFORMATION_SCHEMA.DATABASES d
JOIN INFORMATION_SCHEMA.SCHEMAS s ON d.name = s.DATABASE_NAME
JOIN INFORMATION_SCHEMA.TABLES t ON s.SCHEMA_NAME = t.TABLE_SCHEMA
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME

この方法は、SQL Server Management Studio (SSMS) または PowerShell でスクリプトを実行することを含みます。 スクリプトは、上記のいずれかの方法で使用されるクエリと似ている可能性がありますが、特定のニーズに合わせてカスタマイズすることもできます。

方法 4:サードパーティ製ツールを使用する

それぞれの方法の利点と欠点

最良の方法の選択

最良の方法を選択するには、ニーズと要件を考慮する必要があります。

• シンプルで使いやすい方法が必要な場合は、DMV またはシステム カタログ ビューを使用します。
• より高速なパフォーマンスが必要な場合は、システム カタログ ビューを使用します。
• 柔軟性とカスタマイズ性が必要な場合は、スクリプトを使用します。
• 使いやすさを優先し、コストがかからない場合は、サードパーティ製ツールを使用します。

注:

• 上記に記載されている方法は網羅的なものではありません。他の方法もある可能性があります。
• スクリプトまたはサードパーティ製ツールを使用する場合は、それらを使用する前に必ずドキュメントを確認してください。