SQL チェックシート:SQL サーバーの列情報の収集

• sys.tables について
• sys.columns について
• sys.types について

SQL サーバーでは、sys.tables や sys.columns、sys.types などのシステムテーブルを加えることによって、特定のテーブルの列の詳細(列名、列 ID、列)を収集できます。

クエリ 1:テーブルと object_id のフェッチ

sys.tables について

sys.tables は、システムテーブルで、データベース内のテーブルに関する情報を維持するのに使用されます。データベースに追加されたすべてのテーブルに対して、sys.tables テーブルにレコードが作成されます。各テーブルにあるレコードは1つのみで、テーブル名、テーブルのオブジェクト ID、作成日、変更日などの情報が含まれています。オブジェクト IDは一意で、列の詳細をフェッチするために、このテーブルをその他のシステム (sys.columns) と結合するのに使用します。

次のクエリは、データベース内のすべてのテーブルに object_id をフェッチするのに使用できます。

name AS TableName, object_id AS ObjectID を選択します
sys.tables から
sys.tables から
–– ここで、name = '<TABLENAME>'
–– 行の上のコメントを外し、<Table Name> を追加して、特定のテーブルに詳細をフェッチします

結果は、次のようになります。

クエリ2:列のフェッチ

次の手順でこれらのテーブルに列をフェッチします。これは、データベースの object_id に基づいて、sys.tables を sys.columns に結合します。

sys.columns について

sys.columns は、システムテーブルで、データベース内の列に関する情報を維持するのに使用されます。データベースに追加されたすべての列に対して、sys.columns テーブルにレコードが作成されます。各列にあるレコードは 1 つのみで、次の情報が含まれています。

• Name:列の名前。これは、テーブルオブジェクト内で一意です。
• Object_id: object_id は、列が存在するテーブルに対する一意の識別子です。この列を使用して、異なるテーブルに列をフェッチするために、sys.columns を sys.tables に結合します。
• Column_id:列の ID。これは、テーブルオブジェクト内で一意です。/li>
• user_type_id:列データの型のシステムコード
• max_length:列の最大長(バイト単位)。
• is_nullable:列が null になり得る場合は1になります。

このデータベースには追加の列がありますが、我々の目的のためには、上記の列で十分です。

次のクエリを使用して、object_id 列に従って、sys.columns と sys.tables を結合することによって、列の詳細（対応するテーブルに対して）とそのデータ型をフェッチします。

AB.name AS TableName、TAB.object_id AS ObjectID、COL.name AS ColumnName、COL.user_type_id AS DataTypeID を選択します
sys.columns COLから
INNER JOIN sys.tables TAB
On COL.object_id = TAB.object_id
-- where TAB.name = '<TABLENAME>'
–– 行の上のコメントを外し、<Table Name> を追加して、特定のテーブルに詳細をフェッチします
-- where COL.name = '<COLUMNNAME>'
–– 行の上のコメントを外し、<Column Name> を追加して、特定の列に詳細をフェッチします


結果は次のようになります。

動作中の Sisense:

Explore Dashboard

クエリ 3:Data Type Nameの追加

次の手順で Data Type ID を Data Type Name と置換します。これは、上記のクエリを sys.types に結合することで行うことができます。

sys.types について

sys.types は、システムテーブルで、データベース内の列のデータ型に関する情報を維持するのに使用されます。このテーブルには、各データ型に対する1つの行があり、次の情報が含まれます。

• Name:列の名前。これは、テーブルオブジェクト内で一意です。
• user_type_id:列データの型のシステムコード。これはテーブルに対して一意で、sys.columns テーブルとの結合に使用されます。
• max_length:列の最大長(バイト単位)。

前述の通り、このデータベースには複数の列がありますが、我々の目的のためには、上記の列で十分です。

次のクエリは、user_type_id で sys.typesをsys.columns と結合することによって、Data Type ID を Data Type Name と置換するのに使用できます。

TAB.name AS TableName、TAB.object_id AS ObjectID、COL.name AS ColumnName、TYP.name AS DataTypeName、TYP.max_length AS MaxLength を選択します
sys.columns COLから
INNER JOIN sys.tables TAB
On COL.object_id = TAB.object_id
INNER JOIN sys.types TYP
ON TYP.user_type_id = COL.user_type_id

-- ここで、TAB.name = '<TABLENAME>'
–– 行の上のコメントを外し、<Table Name> を追加して、特定のテーブルに詳細をフェッチします
-- where COL.name = '<COLUMNNAME>'
–– 行の上のコメントを外し、<Column Name> を追加して、特定の列に詳細をフェッチします
-- where TYP.name = '<DATATYPENAME>'
–– 行の上のコメントを外し、<Data Type Name> を追加して、特定のデータ型に詳細をフェッチします


結果は次のテーブルとなり、このテーブルには、我々が求めていた列の内容が含まれています。

SQL サーバーデータへのより深いインサイトを

SQL サーバー、非構造化データソース、フラットファイルなどからのデータを使用して、データベース間の結合を可能にする SQL レポーティングツールをお探しですか?まずは無料のトライアル版をお試しいただき、わずか数回のクリックで SQL サーバーデータベースに接続し、動作中の強力なビジネスインテリジェンスソフトウェアを確認しましょう。