SQL Serverでテーブル一覧やカラム一覧をまとめて確認したいときに使うSQLを紹介します。
ユーザーテーブルの一覧だけなら、まずはsys.tablesを使うと分かりやすいです。この記事では、スキーマ名を含むテーブル一覧と、データ型・NULL許可などを含むカラム一覧を取得します。
対象になるのは、現在接続しているデータベースです。実行前に、SSMSなどの接続先と対象データベースを確認してください。
テーブルを一覧で取得する
テーブル名とスキーマ名を一覧で取得するSQLです。sys.tablesは、データベース内のユーザーテーブルの情報を返します。
コピーしました！
SQL
-- 現在のデータベースにあるユーザーテーブルの一覧
SELECT
DB_NAME() AS database_name,
s.name AS schema_name,
t.name AS table_name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
ORDER BY
s.name,
t.name;実行するとこのような情報が取得できます。
1つのテーブルにつき、1行ずつ表示されます。
例えば、dboスキーマのテーブルだけを確認したい場合は、次のように絞り込みます。
コピーしました！
SQL
-- dboスキーマのユーザーテーブルだけを取得
SELECT
DB_NAME() AS database_name,
s.name AS schema_name,
t.name AS table_name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE s.name = N'dbo'
ORDER BY
s.name,
t.name;dboの部分は、確認したいスキーマ名に変更してください。さらにテーブル名で絞り込みたい場合は、WHERE句の次の行に「AND t.name = N’Person’」のような条件を追加します。
テーブル名とカラムの情報もまとめて取得する
カラム名やデータ型まで確認したい場合は、sys.tables、sys.columns、sys.typesを組み合わせます。
下記のSQLは、そのまま実行するとユーザーテーブルのカラム一覧を取得します。最初の2つの変数を変更すれば、スキーマ名とテーブル名で絞り込めます。
コピーしました！
SQL
-- NULLのままなら絞り込みなし
DECLARE @SchemaName sysname = NULL;
DECLARE @TableName sysname = NULL;
SELECT
DB_NAME() AS database_name,
s.name AS schema_name,
t.name AS table_name,
c.column_id,
c.name AS column_name,
ts.name AS type_schema_name,
ty.name AS data_type,
c.max_length AS max_length_bytes,
c.precision,
c.scale,
c.is_nullable,
c.is_identity,
c.is_computed
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c
ON c.object_id = t.object_id
INNER JOIN sys.types AS ty
ON ty.user_type_id = c.user_type_id
INNER JOIN sys.schemas AS ts
ON ts.schema_id = ty.schema_id
WHERE
(@SchemaName IS NULL OR s.name = @SchemaName)
AND (@TableName IS NULL OR t.name = @TableName)
ORDER BY
s.name,
t.name,
c.column_id;実行するとこのような情報が取得できます。
上記ではテーブル名を指定しましたが、他にも色々な情報を指定できます。指定できる情報の詳細はMicrosoft公式のページでご確認ください。
