PowerShellでできる！SQL Serverの既存テーブルからCREATEスクリプトを生成する方法

2024-07-01

SQL Server で既存のテーブルから CREATE スクリプトを生成する方法

SQL Server Management Studio (SSMS) を使用する

SSMS は、Microsoft が提供する無償のデータベース管理ツールです。SSMS を使用すると、GUI 操作で簡単に CREATE スクリプトを生成できます。

手順:

  1. SSMS で対象のデータベースに接続します。
  2. オブジェクト エクスプローラーで、生成したいスクリプトのテーブルを右クリックします。
  3. メニューから 「タスク」 > 「スクリプトの生成」 を選択します。
  4. 「スクリプト作成の詳細オプション」 ダイアログで、必要なオプションを設定します。
    • 「スクリプトを作成するオブジェクトの種類」 で、**「テーブル」**を選択します。
    • 「依存関係を含める」 を選択すると、関連する制約やインデックスなどのスクリプトも生成されます。
    • 「詳細設定」 ボタンをクリックすると、さらに詳細なオプションを設定できます。
  5. 「OK」 をクリックして、スクリプトの生成を開始します。
  6. 生成されたスクリプトは、「出力」 ウィンドウに表示されます。必要に応じて、テキストエディタで編集してから保存できます。

T-SQL クエリを使用する

T-SQL を使用して、INFORMATION_SCHEMA ビューからテーブル定義に関する情報を取得し、CREATE スクリプトを生成することもできます。

例:

-- 対象のテーブル名を入力してください
DECLARE @tableName NVARCHAR(128) = 'your_table_name';

-- テーブル定義を取得
SELECT
    t.TABLE_CATALOG,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH,
    c.NUMERIC_PRECISION,
    c.NUMERIC_SCALE,
    c.IS_NULLABLE,
    CONSTRAINT_NAME = dc.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.TABLES t
INNER JOIN
    INFORMATION_SCHEMA.COLUMNS c
        ON t.TABLE_CATALOG = c.TABLE_CATALOG
        AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc
        ON t.TABLE_CATALOG = kc.TABLE_CATALOG
        AND t.TABLE_SCHEMA = kc.TABLE_SCHEMA
        AND t.TABLE_NAME = kc.TABLE_NAME
        AND c.COLUMN_NAME = kc.COLUMN_NAME
LEFT JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS dc
        ON t.TABLE_CATALOG = dc.TABLE_CATALOG
        AND t.TABLE_SCHEMA = dc.TABLE_SCHEMA
        AND t.TABLE_NAME = dc.TABLE_NAME
WHERE
    t.TABLE_NAME = @tableName
ORDER BY
    c.ORDINAL_POSITION;

-- CREATE TABLE スクリプトを生成
DECLARE @createTableScript NVARCHAR(MAX);

SET @createTableScript = N'CREATE TABLE ' + QUOTENAME(@tableName) + ' (' + CHAR(13 + 10);

-- 列の定義を追記
SELECT
    @createTableScript = @createTableScript +
        '    ' + QUOTENAME(c.COLUMN_NAME) + ' ' +
        DATA_TYPE_TO_STRING(c.DATA_TYPE) +
        (CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ' (' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) + ')' ELSE '' END) +
        (CASE WHEN c.NUMERIC_PRECISION IS NOT NULL THEN ' (' + CAST(c.NUMERIC_PRECISION AS NVARCHAR(10)) + ',' + CAST(c.NUMERIC_SCALE AS NVARCHAR(10)) + ')' ELSE '' END) +
        (CASE WHEN c.IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END) + ',' + CHAR(13 + 10)
FROM
    INFORMATION_SCHEMA.COLUMNS c
WHERE
    c.TABLE_CATALOG = @tableNameCatalog
    AND c.TABLE_SCHEMA = @tableNameSchema
    AND c.TABLE_NAME = @tableName
ORDER BY
    c.ORDINAL_POSITION;

-- 制約の定義を追記
SELECT
    @createTableScript = @createTableScript +
        '    CONSTRAINT ' + QUOTENAME(dc


サンプルコード：SQL Server で既存のテーブルから CREATE TABLE スクリプトを生成する

-- 対象のテーブル名を入力してください
DECLARE @tableName NVARCHAR(128) = 'your_table_name';

-- テーブル定義を取得
SELECT
    t.TABLE_CATALOG,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH,
    c.NUMERIC_PRECISION,
    c.NUMERIC_SCALE,
    c.IS_NULLABLE,
    CONSTRAINT_NAME = dc.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.TABLES t
INNER JOIN
    INFORMATION_SCHEMA.COLUMNS c
        ON t.TABLE_CATALOG = c.TABLE_CATALOG
        AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc
        ON t.TABLE_CATALOG = kc.TABLE_CATALOG
        AND t.TABLE_SCHEMA = kc.TABLE_SCHEMA
        AND t.TABLE_NAME = kc.TABLE_NAME
        AND c.COLUMN_NAME = kc.COLUMN_NAME
LEFT JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS dc
        ON t.TABLE_CATALOG = dc.TABLE_CATALOG
        AND t.TABLE_SCHEMA = dc.TABLE_SCHEMA
        AND t.TABLE_NAME = dc.TABLE_NAME
WHERE
    t.TABLE_NAME = @tableName
ORDER BY
    c.ORDINAL_POSITION;

-- CREATE TABLE スクリプトを生成
DECLARE @createTableScript NVARCHAR(MAX);

SET @createTableScript = N'CREATE TABLE ' + QUOTENAME(@tableName) + ' (' + CHAR(13 + 10);

-- 列の定義を追記
SELECT
    @createTableScript = @createTableScript +
        '    ' + QUOTENAME(c.COLUMN_NAME) + ' ' +
        DATA_TYPE_TO_STRING(c.DATA_TYPE) +
        (CASE WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ' (' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) + ')' ELSE '' END) +
        (CASE WHEN c.NUMERIC_PRECISION IS NOT NULL THEN ' (' + CAST(c.NUMERIC_PRECISION AS NVARCHAR(10)) + ',' + CAST(c.NUMERIC_SCALE AS NVARCHAR(10)) + ')' ELSE '' END) +
        (CASE WHEN c.IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END) + ',' + CHAR(13 + 10)
FROM
    INFORMATION_SCHEMA.COLUMNS c
WHERE
    c.TABLE_CATALOG = @tableNameCatalog
    AND c.TABLE_SCHEMA = @tableNameSchema
    AND c.TABLE_NAME = @tableName
ORDER BY
    c.ORDINAL_POSITION;

-- 制約の定義を追記
SELECT
    @createTableScript = @createTableScript +
        '    CONSTRAINT ' + QUOTENAME(dc.CONSTRAINT_NAME) + ' ' +
        dc.CONSTRAINT_TYPE + ' (' +
        STRING_AGG(kc.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY kc.ORDINAL_POSITION) +
        ')' + CHAR(13 + 10)
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS dc
LEFT JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc
        ON dc.TABLE_CATALOG = kc.TABLE_CATALOG
        AND dc.TABLE_SCHEMA = kc.TABLE_SCHEMA
        AND dc.TABLE_NAME = kc.TABLE_NAME
        AND dc.CONSTRAINT_NAME = kc.CONSTRAINT_NAME
GROUP BY
    dc.TABLE_CATALOG,
    dc.TABLE_SCHEMA,
    dc.TABLE_NAME,
    dc.CONSTRAINT_NAME,
    dc.CONSTRAINT_TYPE;

-- テーブルコメントを取得
SELECT
    @createTableScript = @createTableScript +
        ISNULL(t.TABLE_COMMENT, '') + CHAR(13 + 10)
FROM
    INFORMATION_SCHEMA.TABLES t
WHERE
    t.TABLE_NAME = @tableName;

-- スクリプトの終了
SET @createTableScript = @createTableScript + ')';

-- 生成されたスクリプトを出力
SELECT @createTable


既存のテーブルから CREATE スクリプトを生成するその他の方法

PowerShell を使用して、SQL Server Management Object (SMO) を操作し、CREATE スクリプトを生成することができます。

サードパーティ製のツールを使用する

Red Gate や ApexSQL などのサードパーティ製ツールでは、GUI 操作で簡単に CREATE スクリプトを生成することができます。これらのツールは、商用版と無償版が用意されています。

オンラインツールを使用する

  • 使用している環境
  • スクリプトに含める情報 (制約、インデックス、コメントなど)
  • 予算
  • 個人趣向

補足:

  • 生成されたスクリプトは、必ず手動で確認してから実行してください。
  • 複雑なテーブルやスキーマの場合は、上記のいずれの方法でも完全なスクリプトが生成されない場合があります。そのような場合は、手動で修正する必要があります。

sql sql-server t-sql

爆速！REGEXP_REPLACEとPATINDEXでVARCHAR型から非数値文字を削除

SQL ServerでVARCHAR型から非数値文字を削除する方法はいくつかありますが、それぞれ速度やパフォーマンスに違いがあります。この解説では、最も高速な方法である REGEXP_REPLACE 関数と PATINDEX 関数を組み合わせた方法を、以下の3つのステップで詳しく説明します。...

 
sql server performance 爆速！REGEXP_REPLACEとPATINDEXでVARCHAR型から非数値文字を削除

SQL Serverで「CREATE OR REPLACE VIEW」をエミュレートする方法: MERGE 構文を使用して既存のビューを更新または作成する

Oracleデータベースでは、既存のビューを置き換えるために CREATE OR REPLACE VIEW 構文を使用できます。一方、SQL Serverにはこの構文が存在せず、既存のビューを置き換えるには、いくつかの代替手段を用いる必要があります。...

 
sql server SQL Serverで「CREATE OR REPLACE VIEW」をエミュレートする方法: MERGE 構文を使用して既存のビューを更新または作成する

SQL Serverにおけるテキストデータの格納：varchar(MAX) vs TEXT vs その他の徹底比較

SQL Serverでテキストデータを格納する場合、varchar(MAX)とTEXTのどちらのデータ型を使用するかを決定する必要があります。それぞれ異なる特性とパフォーマンス上の影響があるため、適切なデータ型を選択することが重要です。varchar(MAX)とTEXTの特徴...

 
sql server performance text SQL Serverにおけるテキストデータの格納：varchar(MAX) vs TEXT vs その他の徹底比較

SQL Server で T-SQL を使って JSON を解析する方法：包括的なガイド

JSON データを解析するには、主に以下の 2 つの方法があります。OPENJSON 関数は、JSON テキストをテーブル形式に変換します。変換されたテーブルには、JSON オブジェクトのプロパティが列として、プロパティの値が行として格納されます。...

 
sql server t json SQL Server で T-SQL を使って JSON を解析する方法：包括的なガイド

SQL SQL SQL SQL Amazon で見る


PowerShellスクリプトでCREATE TABLEステートメントを生成する
PowerShellスクリプトでCREATE TABLEステートメントを生成する

SQL Server Management Studio (SSMS) を開きます。オブジェクト エクスプローラーで、テーブルを含むデータベースを展開します。テーブルを右クリックし、「デザイン」を選択します。テーブル デザイナーで、「ファイル」メニューから、「スクリプトの生成」を選択します。

データベースエンジンチューニングアドバイザーを使ってIDENTITYを追加する方法
データベースエンジンチューニングアドバイザーを使ってIDENTITYを追加する方法

このチュートリアルでは、SQL Server の既存の列に IDENTITY プロパティを追加する方法について、いくつかの方法を説明します。IDENTITY プロパティは、新しい行が挿入されるたびに自動的に増加する一意の値を生成するために使用されます。

GUI vs スクリプト：SQL Serverでデータベースとテーブルを作成する最良の方法
GUI vs スクリプト：SQL Serverでデータベースとテーブルを作成する最良の方法

このチュートリアルでは、SQL Serverを使用してデータベースとテーブルを作成するスクリプトを作成する方法を説明します。データベースとテーブルは、データを整理して格納するために使用される重要なデータベースオブジェクトです。スクリプトを使用すると、これらのオブジェクトを効率的に作成および管理することができます。

SQL Server 2005 で VARCHAR(MAX) を出力するその他の方法：上級者向けガイド
SQL Server 2005 で VARCHAR(MAX) を出力するその他の方法：上級者向けガイド

PRINT ステートメントを使用して VARCHAR(MAX) データ型を出力するには、以下の構文を使用します。ここで、@variable_name は VARCHAR(MAX) データ型の変数名です。例：このコードは、my_variable 変数に格納されている VARCHAR(MAX) データ型を出力します。

T-SQLスクリプトで全貌を把握！ループ処理でntext/nvarchar(max)型データの全テキストを余すことなく表示
T-SQLスクリプトで全貌を把握！ループ処理でntext/nvarchar(max)型データの全テキストを余すことなく表示

SQL Server では、大量のテキストデータを格納するために ntext および nvarchar(max) データ型が使用されます。これらのデータ型は、それぞれ 4GB までのテキストを格納できます。しかし、SSMS (SQL Server Management Studio) でこれらのデータ型からすべてのテキストを表示しようとすると、一部の文字が欠けてしまうことがあります。これは、SSMS のデフォルトの表示形式では、4GB 以上のテキストを適切に処理できないためです。