本ブログでは以前にSQLServerのデータベースから、テーブル定義書のもとになるデータを取得する方法についてご紹介しました。
今回の記事では、もう一歩先に進んで読者の皆様にご利用していただきやすいように、SQLServerからテーブル定義書用のデータを取得するのSQLをユーザー定義関数にして、ご提供したいと思います。
テーブル定義書のもとになるデータは、SQLServerにあらかじめ用意されている「カタログビュー」から取得します。
本記事では、参照するカタログビューの詳細については触れません。
テーブル定義書用のデータを取得する詳細情報がお知りになりたい方は、以下の記事をご覧ください。
SQL Server のカタログビューからテーブルの定義書情報を取得する
SQLServer にはデータベース内のスキーマ、オブジェクト、スカラー型などの情報を取得するために、システムカタログビューが用意されていま...
johobase.com
目次
テーブル定義情報を取得するユーザー定義関数のSQL文
CREATE文
テーブル定義情報を取得する関数のCREATE文の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 |
-- テーブル定義情報を取得するユーザー定義関数を作成する CREATE FUNCTION dbo.get_table_definition ( @schema_name nvarchar(256), @table_name nvarchar(256) ) RETURNS TABLE RETURN -- テーブル定義情報を取得するSQLのSELECT文 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 unique_keys.key_ordinal is null THEN CONVERT(bit, 'FALSE') ELSE CONVERT(bit, 'TRUE') END AS is_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 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 GO |
DROP文
テーブル定義情報を取得する関数が既に存在する場合に、いったん削除するDROP文のSQLも一応掲載しておきます。
|
1 2 3 |
-- テーブル定義情報を取得するユーザー定義関数が存在する場合はいったん削除する DROP FUNCTION IF EXISTS dbo.get_table_definition; GO |
上記のDROP文のSQLは、ユーザー定義関数を作成するスクリプトを改変する場合に、既に存在している関数を削除して再作成する場合に使ってください。
テーブル定義情報を取得するユーザー定義関数の使い方
テーブル定義情報を取得するために、ユーザー定義関数のCREATE文のSQLをMicrosoft SQL Server Management Studioなどのツールを使って実行してください。
SQLを実行すると「dbo.get_table_definition」というユーザー定義関数が作成されます。
後は、関数を呼び出して実行するのみです。
本記事で作成する関数は、テーブル形式（表形式）のデータを取得して返しますので、SELECT文のFROM句に記載して呼び出します。
以下に例として、スキーマ名が「abc」テーブル名が「SAMPLE_TABLE」のテーブルを取得する例を示します。
テーブルの定義情報を取得するSQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- abc.SAMPLE_TABLEテーブルの定義情報を取得する SELECT table_name, schema_name, table_description, column_id, column_name, data_type, digits, nullable, identity_set, primary_key_ordinal, is_unique, default_value, column_description FROM dbo.get_table_definition('abc', 'SAMPLE_TABLE') order by column_id; |
上記のSQLを実行するとabc.SAMPLE_TABLEテーブルの定義情報を取得することができます。
読者の皆さんが、すぐにテーブル定義書を作成するもとになるデータを取得するユーザー定義関数を使えるように、abcスキーマとSAMPLE_TABLEテーブルを作成するSQLスクリプトも以下に掲載しておきます。
adbスキーマを作成するSQL
|
1 2 3 4 5 |
DROP SCHEMA IF EXISTS abc; GO CREATE SCHEMA abc; GO |
SAMPLE_TABLEテーブルを作成する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 |
IF EXISTS (SELECT * FROM sys.fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'abc', N'TABLE',N'SAMPLE_TABLE', NULL,NULL)) EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'abc', @level1type=N'TABLE',@level1name=N'SAMPLE_TABLE' GO IF EXISTS (SELECT * FROM sys.fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'abc', N'TABLE',N'SAMPLE_TABLE', N'COLUMN',N'TINYINT_VALUE')) EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'abc', @level1type=N'TABLE',@level1name=N'SAMPLE_TABLE', @level2type=N'COLUMN',@level2name=N'TINYINT_VALUE' GO IF EXISTS (SELECT * FROM sys.fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'abc', N'TABLE',N'SAMPLE_TABLE', N'COLUMN',N'INT_VALUE')) EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'abc', @level1type=N'TABLE',@level1name=N'SAMPLE_TABLE', @level2type=N'COLUMN',@level2name=N'INT_VALUE' GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[abc].[SAMPLE_TABLE]') AND type in (N'U')) ALTER TABLE [abc].[SAMPLE_TABLE] DROP CONSTRAINT IF EXISTS [DF_SAMPLE_TABLE_DECIMAL_VALUE] GO DROP TABLE IF EXISTS [abc].[SAMPLE_TABLE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [abc].[SAMPLE_TABLE]( [INT_VALUE] [int] IDENTITY(1,1) NOT NULL, [TINYINT_VALUE] [tinyint] NOT NULL, [VARCHAR_VALUE] [varchar](50) COLLATE Japanese_CI_AS NOT NULL, [DATE_VALUE] [date] NULL, [DECIMAL_VALUE] [decimal](12, 2) NOT NULL, [NVARCHAR_VALUE] [nvarchar](1000) COLLATE Japanese_CI_AS NULL, CONSTRAINT [PK_SAMPLE_TABLE] PRIMARY KEY CLUSTERED ( [INT_VALUE] ASC, [TINYINT_VALUE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY], UNIQUE NONCLUSTERED ( [DATE_VALUE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [abc].[SAMPLE_TABLE] ADD CONSTRAINT [DF_SAMPLE_TABLE_DECIMAL_VALUE] DEFAULT ((0)) FOR [DECIMAL_VALUE] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IDENTITY列の主キー1' , @level0type=N'SCHEMA',@level0name=N'abc', @level1type=N'TABLE',@level1name=N'SAMPLE_TABLE', @level2type=N'COLUMN',@level2name=N'INT_VALUE' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主キー2' , @level0type=N'SCHEMA',@level0name=N'abc', @level1type=N'TABLE',@level1name=N'SAMPLE_TABLE', @level2type=N'COLUMN',@level2name=N'TINYINT_VALUE' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'テーブル定義情報を取得するサンプルテーブル' , @level0type=N'SCHEMA',@level0name=N'abc', @level1type=N'TABLE',@level1name=N'SAMPLE_TABLE' GO |
スキーマの作成とテーブルの作成を行うSQLスクリプトを実行し、作成したテーブル定義情報を取得する関数の「dbo.get_table_definition」を実行していただくと、以下のような結果表が取得できると思います。
スキーマの作成については、以下の記事を参考にしてください。
データベースのスキーマを作成するCREATE SCHEMAと削除するDROP SCHEMA SQL
データベースには、スキーマ（Schema）と呼ばれるオブジェクトがあります。
スキーマとは、データベース内のテーブルやビューなどのオブ...
johobase.com
テーブルの作成については、以下の記事を参考にしてください。
データベースのテーブルを作成するSQL CREATE TABLE
SQL文では、既存のテーブルに対してINSERT、UPDATE、DELETEなどのステートメントを使用して、テーブルやビューのデータ（レコー...
johobase.com
