SQLServer にはデータベース内のスキーマ、オブジェクト、スカラー型などの情報を取得するために、システムカタログビューが用意されています。
今回はそのカタログビューを使用して、テーブルと列 (カラム) の情報を取得する SQL を組んでみたいと思います。
カタログビューで取得できる情報を使用すると、テーブル定義書のもとになるデータを作成できます。
カタログビューからは、テーブルと列に関する様々な情報を取得できますが、情報が多すぎるとわかりづらくなるので、基本的な情報のみ取得することにします。
使用するカタログビュー
テーブルと列の情報を取得するために使用するカタログビューは以下になります。
取得するデータは基本的な情報のみに絞り、「テーブル名」「スキーマ名」「テーブルの説明」「列の説明」「列 ID」「列名」「桁数」「Null を許容」「データ型」「プライマリキー」「ID 列の IDENTITY 設定」「初期値 (既定値またはバインド)」の 12 種類にします。
使用するカタログビュー一覧
| 名称 | 説明 |
|---|---|
| sys.tables | テーブル カタログビュー |
| sys.schemas | スキーマ カタログビュー |
| sys.extended_properties | 拡張プロパティ カタログビュー |
| sys.columns | 列 (カラム) カタログビュー |
| sys.types | 型 カタログビュー |
| sys.identity_columns | ID 列 カタログビュー |
| sys.index_columns | インデックス列 カタログビュー |
| sys.key_constraints | 主キー制約または一意制約 カタログビュー |
| sys.default_constraints | デフォルト制約 カタログビュー |
取得するデータ
| 名称 | 取得するデータ |
|---|---|
| sys.tables | テーブル名 |
| sys.schemas | スキーマ名 |
| sys.extended_properties | テーブルの説明 列の説明 |
| sys.columns | 列 ID 列名 桁数 Null を許容 |
| sys.types | 型名 (データ型) |
| sys.identity_columns | ID 列の IDENTITY 設定 |
| sys.index_columns | プライマリキー |
| sys.key_constraints | |
| sys.default_constraints | 初期値 (既定値またはバインド) |
テーブル情報
テーブル情報は
・テーブルカタログビューの sys.tables
・スキーマカタログビューの sys.schemas
・拡張プロパティカタログビューのsys.extended_properties
の３つから取得します。
sys.tables
テーブルカタログビューから「オブジェクト ID」と「テーブル名」を取得します。
|
1 2 3 4 5 6 7 8 |
SELECT -- オブジェクト ID tables.object_id, -- テーブル名 tables.name AS table_name FROM -- テーブル カタログビュー sys.tables |
「オブジェクトID」はオブジェクトを一意に判定するためのキーとなり、別のカタログビューとの結合に使用するので取得しておきます。
sys.schemas
スキーマカタログビューからは「スキーマ名」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT tables.object_id, tables.name, -- スキーマ名 schemas.name AS schema_name FROM -- テーブル カタログビュー sys.tables INNER JOIN -- スキーマ カタログビュー sys.schemas ON tables.schema_id = schemas.schema_id |
テーブルカタログビューとスキーマカタログビューを schema_id で内部結合します。
ここではスキーマ名を取得するために sys.schemas を結合していますが、スキーマ名は SCHEMA_NAME 関数で取得することもできます。
|
1 2 3 4 5 6 7 8 |
SELECT tables.object_id, tables.name AS table_name, -- スキーマ名 SCHEMA_NAME(tables.schema_id) AS schema_name FROM -- テーブル カタログビュー sys.tables |
sys.extended_properties
拡張プロパティカタログビューからは「テーブルの説明」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT tables.object_id, tables.name AS table_name, schemas.name AS schema_name, -- テーブルの説明 table_descriptions.value AS table_description FROM -- テーブル カタログビュー sys.tables INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id LEFT OUTER JOIN -- 拡張プロパティ カタログビュー sys.extended_properties AS table_descriptions ON table_descriptions.class = 1 AND tables.object_id = table_descriptions.major_id AND table_descriptions.minor_id = 0 |
拡張プロパティカタログビューの class が「1 = オブジェクトまたは列」のデータに絞り込みます。
テーブルカタログビューと拡張プロパティカタログビューを sys.tables.object_id と sys.extended_properties.major_id (table_descriptions.major_id) で外部結合します。(class が 1 の場合、major_id は object_id になります。)
拡張プロパティカタログビューの minor_id に「0」を指定してテーブルの説明に絞り込みます。
テーブル情報の取得は以上です。
次に、列情報を取得します。
列情報
列情報は
・列 (カラム) カタログビューの sys.columns
・型カタログビューの sys.types
・ID 列カタログビューの sys.identity_columns
・デフォルト制約カタログビューの sys.default_constraints
・拡張プロパティカタログビューの sys.extended_properties
・インデックス列カタログビューの sys.index_columns
・主キー制約または一意制約カタログビューの sys.key_constraints
の７つから取得します。
sys.columns
列 (カラム) カタログビューから「オブジェクト ID」「列 ID」「列名」と、「桁数」のもとになる「列の最大長 (バイト単位)」「有効桁数 (数値の場合)」「小数点以下桁数 (数値の場合)」の３つのデータと、「Null を許容するかどうか」「ID 列かどうか」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT -- オブジェクト ID columns.object_id, -- 列 ID columns.column_id, -- 列名 columns.name AS column_name, -- 列の最大長 (バイト単位) columns.max_length, -- 有効桁数 (数値の場合) columns.precision, -- 小数点以下桁数 (数値の場合) columns.scale, -- Null を許容するかどうか columns.is_nullable, -- ID 列かどうか columns.is_identity FROM -- 列 (カラム) カタログビュー sys.columns |
列 (カラム) カタログビュー (sys.columns) はテーブルの情報以外も返すので、この時点では実行結果にビューの情報も含まれます。
sys.types
型カタログビューからは「型名 (データ型)」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT columns.object_id, columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, -- 型名 (データ型) types.name AS data_type FROM -- 列 (カラム) カタログビュー sys.columns INNER JOIN -- 型 カタログビュー sys.types ON columns.user_type_id = types.user_type_id |
列カタログビューと型カタログビューは user_type_id で内部結合します。
ここでは型名を取得するために sys.types を結合していますが、型名は TYPE_NAME 関数で取得することもできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT columns.object_id, columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, -- 型名 (データ型) TYPE_NAME(columns.user_type_id) AS data_type FROM -- 列 (カラム) カタログビュー sys.columns |
sys.identity_columns
ID 列カタログビューからは「ID 列の IDENTITY 設定」のもとになる「シード値 (IDENTITY シード)」と「インクリメント値 (ID の増分)」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SELECT columns.object_id, columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, types.name AS data_type, -- シード値 (IDENTITY シード) identity_columns.seed_value, -- インクリメント値 (ID の増分) identity_columns.increment_value FROM -- 列 (カラム) カタログビュー sys.columns INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN -- ID 列 カタログビュー sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id |
列カタログビューとID 列カタログビューは object_id および column_id で外部結合します。
sys.default_constraints
デフォルト制約カタログビューからは「初期値 (既定値またはバインド)」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SELECT columns.object_id, columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, types.name AS data_type, identity_columns.seed_value, identity_columns.increment_value, -- 初期値 (既定値またはバインド) default_constraints.definition AS default_value_or_binding FROM -- 列 (カラム) カタログビュー sys.columns INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id LEFT OUTER JOIN -- デフォルト制約 カタログビュー sys.default_constraints ON columns.default_object_id = default_constraints.object_id |
列カタログビューとデフォルト制約カタログビューを sys.columns.default_object_id と sys.default_constraints.object_id で外部結合します。
sys.extended_properties
拡張プロパティカタログビューからは「列の説明」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SELECT columns.object_id, columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, types.name AS data_type, identity_columns.seed_value, identity_columns.increment_value, default_constraints.definition AS default_value_or_binding, -- 列の説明 column_descriptions.value AS column_description FROM -- 列 (カラム) カタログビュー sys.columns INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id LEFT OUTER JOIN sys.default_constraints ON columns.default_object_id = default_constraints.object_id LEFT OUTER JOIN -- 拡張プロパティ カタログビュー sys.extended_properties AS column_descriptions ON column_descriptions.class = 1 AND columns.object_id = column_descriptions.major_id AND columns.column_id = column_descriptions.minor_id |
テーブルの説明の時と同様に、拡張プロパティカタログビューの class が「1 = オブジェクトまたは列」のデータに絞り込みます。
列カタログビューと拡張プロパティカタログビューを sys.columns.object_id と sys.extended_properties.major_id (column_descriptions.major_id) および sys.columns.column_id と sys.extended_properties.minor_id (column_descriptions.minor_id) で外部結合します。
sys.index_columns, sys.key_constraints
プライマリキー情報を取得するために、インデックス列カタログビューと主キー制約または一意制約カタログビューから「プライマリキー列の順番」を取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
SELECT columns.object_id, columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, types.name AS data_type, identity_columns.seed_value, identity_columns.increment_value, default_constraints.definition AS default_value_or_binding, column_descriptions.value AS column_description, -- プライマリキー列の順番 primary_keys.key_ordinal as primary_key_ordinal FROM -- 列 (カラム) カタログビュー sys.columns INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id LEFT OUTER JOIN sys.default_constraints ON columns.default_object_id = default_constraints.object_id LEFT OUTER JOIN sys.extended_properties AS column_descriptions ON column_descriptions.class = 1 AND columns.object_id = column_descriptions.major_id AND columns.column_id = column_descriptions.minor_id LEFT OUTER JOIN /* プライマリキーのサブクエリ */ ( SELECT index_columns.object_id, index_columns.column_id, index_columns.key_ordinal FROM -- インデックス列 カタログビュー sys.index_columns INNER JOIN -- 主キー制約または一意制約 カタログビュー sys.key_constraints ON key_constraints.type = 'PK' AND index_columns.object_id = key_constraints.parent_object_id AND index_columns.index_id = key_constraints.unique_index_id ) AS primary_keys ON columns.object_id = primary_keys.object_id AND columns.column_id = primary_keys.column_id |
まず、インデックス列カタログビューと主キー制約または一意制約カタログビューを結合します。
主キー制約または一意制約カタログビューの type に「PK」を指定してプライマリキーに絞り込みます。
インデックス列カタログビューと主キー制約または一意制約カタログビューを index_columns.object_id と key_constraints.parent_object_id および index_columns.index_id と key_constraints.unique_index_id で内部結合します。
結合した結果をサブクエリとして、列カタログビューと object_id および column_id で外部結合します。
ここまでで列情報が取得できたので、先に取得したテーブル情報と合わせます。
テーブル情報 + 列情報
テーブル情報を取得する SQL と 列情報を取得する SQL を組み合わせて１つの SQL にします。
テーブル情報と列情報を合体
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
SELECT tables.object_id, tables.name AS table_name, schemas.name AS schema_name, table_descriptions.value AS table_description, -- ↓ 列情報の列を追加 columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, types.name AS data_type, identity_columns.seed_value, identity_columns.increment_value, default_constraints.definition AS default_value_or_binding, column_descriptions.value AS column_description, primary_keys.key_ordinal as primary_key_ordinal FROM sys.tables INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id LEFT OUTER JOIN sys.extended_properties AS table_descriptions ON table_descriptions.class = 1 AND tables.object_id = table_descriptions.major_id AND table_descriptions.minor_id = 0 -- ↓ テーブル カタログビューと列 カタログビューを結合 INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id -- ↑ テーブル カタログビューと列 カタログビューを結合 INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id LEFT OUTER JOIN sys.default_constraints ON columns.default_object_id = default_constraints.object_id LEFT OUTER JOIN sys.extended_properties AS column_descriptions ON column_descriptions.class = 1 AND columns.object_id = column_descriptions.major_id AND columns.column_id = column_descriptions.minor_id LEFT OUTER JOIN ( SELECT index_columns.object_id, index_columns.column_id, index_columns.key_ordinal FROM sys.index_columns INNER JOIN sys.key_constraints ON key_constraints.type = 'PK' AND index_columns.object_id = key_constraints.parent_object_id AND index_columns.index_id = key_constraints.unique_index_id ) AS primary_keys ON columns.object_id = primary_keys.object_id AND columns.column_id = primary_keys.column_id |
テーブル情報と列情報は、tables.object_id と columns.object_id で内部結合します。
これで、列情報がテーブルの情報のみに絞り込まれます。
列情報の変換
新たに取得したい列情報の「桁数」「Null を許容するかどうか」「ID 列の IDENTITY 設定」「初期値」の４つのデータを、先に取得した情報をもとに変換して作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
SELECT tables.object_id, tables.name AS table_name, schemas.name AS schema_name, table_descriptions.value AS table_description, columns.object_id, columns.column_id, columns.name AS column_name, columns.max_length, columns.precision, columns.scale, columns.is_nullable, columns.is_identity, types.name AS data_type, identity_columns.seed_value, identity_columns.increment_value, default_constraints.definition AS default_value_or_binding, column_descriptions.value AS column_description, primary_keys.key_ordinal as primary_key_ordinal, -- 桁数 -- 型名 (データ型) ごとに必要な桁数文字列に変換 CASE -- varchar, nvarchar, varbinary で列の最大長 (バイト単位) が「-1」の場合は「MAX」 WHEN types.name in ('varchar', 'nvarchar', 'varbinary') and columns.max_length = -1 THEN 'MAX' -- decimal, numeric の場合は有効桁数と小数以下桁数をカンマ区切りに変換 WHEN types.name in ('decimal', 'numeric') THEN CONVERT(NVARCHAR(10), columns.precision) + ', ' + CONVERT(NVARCHAR(10), columns.scale) -- binary, char, varbinary, varchar の場合は列の最大長 (バイト単位) をそのまま文字列に変換 WHEN types.name in ('binary', 'char', 'varbinary', 'varchar') THEN CONVERT(NVARCHAR(10),columns.max_length) -- nchar, nvarchar の場合は列の最大長 (バイト単位) を半分 (1/2) にして文字列に変換 WHEN types.name in ('nchar', 'nvarchar') THEN CONVERT(NVARCHAR(10),(columns.max_length / 2)) -- datetime2, datetimeoffset, time の場合は小数以下桁数を文字列に変換 (秒未満) WHEN types.name in ('datetime2', 'datetimeoffset', 'time') THEN CONVERT(NVARCHAR(10),columns.scale) ELSE -- その他は空文字列にする '' END AS digits, -- Null を許容するかどうか CASE -- Nullを許容する場合 (is_nullable が 1) の場合は「Y」その他は「N」に変換 WHEN columns.is_nullable = 1 THEN 'Y' ELSE 'N' END AS nullable, -- ID 列の IDENTITY 設定 -- シード値 (IDENTITY シード)とインクリメント値 (ID の増分) をカンマ区切りにして Identity(X, X) の形式に変換 'Identity(' + CONVERT(NVARCHAR(10), identity_columns.seed_value) + ', ' + CONVERT(NVARCHAR(10), identity_columns.increment_value) + ')' AS identity_set, -- 初期値 -- 初期値の値には「(」と「)」が含まれるので除去 CASE WHEN left(default_constraints.definition, 2) = '((' AND RIGHT(default_constraints.definition, 2) = '))' THEN SUBSTRING(default_constraints.definition, 3, LEN(default_constraints.definition) - 4) WHEN left(default_constraints.definition, 1) = '(' AND RIGHT(default_constraints.definition, 1) = ')' THEN SUBSTRING(default_constraints.definition, 2, LEN(default_constraints.definition) - 2) ELSE NULL END AS default_value FROM sys.tables INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id LEFT OUTER JOIN sys.extended_properties AS table_descriptions ON table_descriptions.class = 1 AND tables.object_id = table_descriptions.major_id AND table_descriptions.minor_id = 0 INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id LEFT OUTER JOIN sys.default_constraints ON columns.default_object_id = default_constraints.object_id LEFT OUTER JOIN sys.extended_properties AS column_descriptions ON column_descriptions.class = 1 AND columns.object_id = column_descriptions.major_id AND columns.column_id = column_descriptions.minor_id LEFT OUTER JOIN ( SELECT index_columns.object_id, index_columns.column_id, index_columns.key_ordinal FROM sys.index_columns INNER JOIN sys.key_constraints ON key_constraints.type = 'PK' AND index_columns.object_id = key_constraints.parent_object_id AND index_columns.index_id = key_constraints.unique_index_id ) AS primary_keys ON columns.object_id = primary_keys.object_id AND columns.column_id = primary_keys.column_id |
「桁数」は「型名」と「列の最大長 (バイト単位) 」「有効桁数」「小数以下桁数」をもとに変換します。
「Null を許容するかどうか」は Null を許容する場合は「Y (Yes)」許容しない場合は「N (No)」に変換します。
「ID 列の IDENTITY 設定」は「Identity(シード値 (IDENTITY シード), インクリメント値 (ID の増分))」に形に変換します。
「初期値 (既定値またはバインド)」は前後の「(」と「)」を除去します。
最後に不要な列を取り除き、列の順番を入れ替えます。
また、今の状態では全てのテーブルのレコードが取得されるので、スキーマ名とテーブル名を指定できるようにします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 |
DECLARE @schema_name NVARCHAR(256) DECLARE @table_name NVARCHAR(256) USE AdventureWorks2017 SET @schema_name = 'dbo' SET @table_name = 'ErrorLog' SELECT tables.name AS table_name, schemas.name AS schema_name, table_descriptions.value AS table_description, columns.column_id, columns.name AS column_name, types.name AS data_type, CASE WHEN types.name in ('varchar', 'nvarchar', 'varbinary') and columns.max_length = -1 THEN 'MAX' WHEN types.name in ('decimal', 'numeric') THEN CONVERT(NVARCHAR(10), columns.precision) + ', ' + CONVERT(NVARCHAR(10), columns.scale) WHEN types.name in ('binary', 'char', 'varbinary', 'varchar') THEN CONVERT(NVARCHAR(10),columns.max_length) WHEN types.name in ('nchar', 'nvarchar') THEN CONVERT(NVARCHAR(10),(columns.max_length / 2)) WHEN types.name in ('datetime2', 'datetimeoffset', 'time') THEN CONVERT(NVARCHAR(10),columns.scale) ELSE '' END AS digits, CASE WHEN columns.is_nullable = 1 THEN 'Y' ELSE 'N' END AS nullable, 'Identity(' + CONVERT(NVARCHAR(10), identity_columns.seed_value) + ', ' + CONVERT(NVARCHAR(10), identity_columns.increment_value) + ')' AS identity_set, primary_keys.key_ordinal as primary_key_ordinal, CASE WHEN left(default_constraints.definition, 2) = '((' AND RIGHT(default_constraints.definition, 2) = '))' THEN SUBSTRING(default_constraints.definition, 3, LEN(default_constraints.definition) - 4) WHEN left(default_constraints.definition, 1) = '(' AND RIGHT(default_constraints.definition, 1) = ')' THEN SUBSTRING(default_constraints.definition, 2, LEN(default_constraints.definition) - 2) ELSE NULL END AS default_value, column_descriptions.value AS column_description FROM sys.tables INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id -- ↓ テーブルを絞り込むために条件を追加 AND schemas.name = @schema_name AND tables.name = @table_name -- ↑ テーブルを絞り込むために条件を追加 LEFT OUTER JOIN sys.extended_properties AS table_descriptions ON table_descriptions.class = 1 AND tables.object_id = table_descriptions.major_id AND table_descriptions.minor_id = 0 INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id LEFT OUTER JOIN sys.default_constraints ON columns.default_object_id = default_constraints.object_id LEFT OUTER JOIN sys.extended_properties AS column_descriptions ON column_descriptions.class = 1 AND columns.object_id = column_descriptions.major_id AND columns.column_id = column_descriptions.minor_id LEFT OUTER JOIN ( SELECT index_columns.object_id, index_columns.column_id, index_columns.key_ordinal FROM sys.index_columns INNER JOIN sys.key_constraints ON key_constraints.type = 'PK' AND index_columns.object_id = key_constraints.parent_object_id AND index_columns.index_id = key_constraints.unique_index_id ) AS primary_keys ON columns.object_id = primary_keys.object_id AND columns.column_id = primary_keys.column_id ORDER BY schema_name, table_name, column_id |
上記の SQL では Microsoft が公開しているデータベースの「AdventureWorks2017」にある dbo.ErrorLog テーブルの情報を取得しています。
実行すると以下のようになります。
SQL Server のカタログビューからはテーブルや列の他にも、ビューやストアドプロシージャをはじめとした様々な情報を取得することができます。例えばこの記事でご紹介した情報の他にもテーブルのインデックスやデータベース自体の情報なども取得可能です。
参考URL
AdventureWorks2017データベースのダウンロード
追記: UNIQUE制約の取得を追加
上記でご紹介したSQLでは、UNIQUE制約（一意制約）が指定された列に対する情報が取得されていませんでしたので、UNIQUE制約が指定されている列の情報を取得するステートメント（命令文）を追加しました。
以下、追加後のSQLになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
DECLARE @schema_name NVARCHAR(256) DECLARE @table_name NVARCHAR(256) USE AdventureWorks2017 -- USE AdventureWorks2019 SET @schema_name = 'dbo' SET @table_name = 'ErrorLog' SELECT tables.name AS table_name, schemas.name AS schema_name, table_descriptions.value AS table_description, columns.column_id, columns.name AS column_name, types.name AS data_type, CASE WHEN types.name in ('varchar', 'nvarchar', 'varbinary') and columns.max_length = -1 THEN 'MAX' WHEN types.name in ('decimal', 'numeric') THEN CONVERT(NVARCHAR(10), columns.precision) + ', ' + CONVERT(NVARCHAR(10), columns.scale) WHEN types.name in ('binary', 'char', 'varbinary', 'varchar') THEN CONVERT(NVARCHAR(10),columns.max_length) WHEN types.name in ('nchar', 'nvarchar') THEN CONVERT(NVARCHAR(10),(columns.max_length / 2)) WHEN types.name in ('datetime2', 'datetimeoffset', 'time') THEN CONVERT(NVARCHAR(10),columns.scale) ELSE '' END AS digits, CASE WHEN columns.is_nullable = 1 THEN 'Y' ELSE 'N' END AS nullable, 'Identity(' + CONVERT(NVARCHAR(10), identity_columns.seed_value) + ', ' + CONVERT(NVARCHAR(10), identity_columns.increment_value) + ')' AS identity_set, primary_keys.key_ordinal as primary_key_ordinal, -- ↓ UNIQUE制約の列情報を取得 CASE WHEN unique_keys.key_ordinal is null THEN CONVERT(bit, 'FALSE') ELSE CONVERT(bit, 'TRUE') END AS is_unique, -- ↑ UNIQUE制約の列情報を取得 CASE WHEN left(default_constraints.definition, 2) = '((' AND RIGHT(default_constraints.definition, 2) = '))' THEN SUBSTRING(default_constraints.definition, 3, LEN(default_constraints.definition) - 4) WHEN left(default_constraints.definition, 1) = '(' AND RIGHT(default_constraints.definition, 1) = ')' THEN SUBSTRING(default_constraints.definition, 2, LEN(default_constraints.definition) - 2) ELSE NULL END AS default_value, column_descriptions.value AS column_description FROM sys.tables INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id AND schemas.name = @schema_name AND tables.name = @table_name LEFT OUTER JOIN sys.extended_properties AS table_descriptions ON table_descriptions.class = 1 AND tables.object_id = table_descriptions.major_id AND table_descriptions.minor_id = 0 INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id INNER JOIN sys.types ON columns.user_type_id = types.user_type_id LEFT OUTER JOIN sys.identity_columns ON columns.object_id = identity_columns.object_id AND columns.column_id = identity_columns.column_id LEFT OUTER JOIN sys.default_constraints ON columns.default_object_id = default_constraints.object_id LEFT OUTER JOIN sys.extended_properties AS column_descriptions ON column_descriptions.class = 1 AND columns.object_id = column_descriptions.major_id AND columns.column_id = column_descriptions.minor_id LEFT OUTER JOIN ( SELECT index_columns.object_id, index_columns.column_id, index_columns.key_ordinal FROM sys.index_columns INNER JOIN sys.key_constraints ON key_constraints.type = 'PK' AND index_columns.object_id = key_constraints.parent_object_id AND index_columns.index_id = key_constraints.unique_index_id ) AS primary_keys ON columns.object_id = primary_keys.object_id AND columns.column_id = primary_keys.column_id -- ↓ UNIQUE制約の列情報を取得 LEFT OUTER JOIN ( SELECT index_columns.object_id, index_columns.column_id, index_columns.key_ordinal FROM sys.index_columns INNER JOIN sys.key_constraints ON key_constraints.type = 'UQ' AND index_columns.object_id = key_constraints.parent_object_id AND index_columns.index_id = key_constraints.unique_index_id ) AS unique_keys ON columns.object_id = unique_keys.object_id AND columns.column_id = unique_keys.column_id -- ↑ UNIQUE制約の列情報を取得 ORDER BY schema_name, table_name, column_id; |
