SQL Serverでスクリプトを生成する

SQLServer

Last updated at 2025-02-12Posted at 2025-02-11

はじめに

SSMSを使うとSQL ServerのCREATE文を簡単に生成することが出来ます。
データベースのオブジェクトをスクリプトで管理する際に、余計なものが出力されていたり、必要な情報が入っていなかったりします。

そこで、良く使いそうな設定を纏めました。

スクリプトの生成手順

１．データベースを右クリック
　　⇒ タスク
　　⇒ スクリプトの生成
　　を選択します。

２．何も考えず次へ進みます。

３．出力するオブジェクトを選択して次へ進みます。

４．出力方法を選択して次へ進みます。

５．設定を確認して次へ進みます。

６．スクリプトが生成されます。

詳細設定

デフォルトの設定は以下の通りです。

サンプルとして以下のようなテーブルのスクリプトを生成します。

デフォルト設定

詳細設定を何も変更せずに生成すると以下のようになります。

デフォルト設定で生成

USE [excel_db]
GO
/****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 21:47:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_student](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NOT NULL,
    [family_name] [nchar](50) NOT NULL,
    [first_name] [nchar](50) NOT NULL,
    [department] [nchar](20) NOT NULL,
    [subject] [nchar](20) NOT NULL,
    [entrance_date] [date] NOT NULL,
    [delete_flag] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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 [dbo].[m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
GO

インデックスのスクリプトを作成（デフォルト値：False）

インデックスのスクリプトを作成を「True」に変更して生成すると以下のようになります。

インデックスのスクリプトを作成：True

USE [excel_db]
GO
/****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 22:39:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_student](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NOT NULL,
    [family_name] [nchar](50) NOT NULL,
    [first_name] [nchar](50) NOT NULL,
    [department] [nchar](20) NOT NULL,
    [subject] [nchar](20) NOT NULL,
    [entrance_date] [date] NOT NULL,
    [delete_flag] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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
+ SET ANSI_PADDING ON
+ GO
+ /****** Object:  Index [i_m_student]    Script Date: 2025/02/11 22:39:18 ******/
+ CREATE NONCLUSTERED INDEX [i_m_student] ON [dbo].[m_student]
+ (
+     [subject] ASC,
+     [department] ASC
+ )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
+ GO
ALTER TABLE [dbo].[m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
GO

トリガーのスクリプトを作成（デフォルト値：False）

トリガーのスクリプトを作成を「True」に変更して生成すると以下のようになります。

トリガーのスクリプトを作成：True

USE [excel_db]
GO
/****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 22:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_student](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NOT NULL,
    [family_name] [nchar](50) NOT NULL,
    [first_name] [nchar](50) NOT NULL,
    [department] [nchar](20) NOT NULL,
    [subject] [nchar](20) NOT NULL,
    [entrance_date] [date] NOT NULL,
    [delete_flag] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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 [dbo].[m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
GO
+ /****** Object:  Trigger [dbo].[delete_m_student]    Script Date: 2025/02/11 22:50:37 ******/
+ SET ANSI_NULLS ON
+ GO
+ SET QUOTED_IDENTIFIER ON
+ GO
+ 
+ CREATE TRIGGER [dbo].[delete_m_student]
+ ON [dbo].[m_student]
+ AFTER DELETE
+ AS
+ BEGIN
+ INSERT INTO [bk_m_student]
+      ( [operation]
+      , [id]
+      , [number]
+      , [family_name]
+      , [first_name]
+      , [department]
+      , [subject]
+      , [entrance_date]
+      , [delete_flag])
+ SELECT 'D' AS operation
+      , id
+      , number
+      , family_name
+      , first_name
+      , department
+      , subject
+      , entrance_date
+      , delete_flag
+   FROM deleted
+ END
+ GO
+ ALTER TABLE [dbo].[m_student] ENABLE TRIGGER [delete_m_student]
+ GO
+ /****** Object:  Trigger [dbo].[insert_m_student]    Script Date: 2025/02/11 22:50:37 ******/
+ SET ANSI_NULLS ON
+ GO
+ SET QUOTED_IDENTIFIER ON
+ GO
+ 
+ 
+ CREATE   TRIGGER [dbo].[insert_m_student]
+ ON [dbo].[m_student]
+ AFTER INSERT
+ AS
+ BEGIN
+ INSERT INTO [bk_m_student]
+      ( [operation]
+      , [id]
+      , [number]
+      , [family_name]
+      , [first_name]
+      , [department]
+      , [subject]
+      , [entrance_date]
+      , [delete_flag])
+ SELECT 'I' AS operation
+      , id
+      , number
+      , family_name
+      , first_name
+      , department
+      , subject
+      , entrance_date
+      , delete_flag
+   FROM inserted
+ END
+ GO
+ ALTER TABLE [dbo].[m_student] ENABLE TRIGGER [insert_m_student]
+ GO
+ /****** Object:  Trigger [dbo].[update_m_student]    Script Date: 2025/02/11 22:50:37 ******/
+ SET ANSI_NULLS ON
+ GO
+ SET QUOTED_IDENTIFIER ON
+ GO
+ CREATE TRIGGER [dbo].[update_m_student]
+ ON [dbo].[m_student]
+ AFTER UPDATE
+ AS
+ BEGIN
+ INSERT INTO [bk_m_student]
+      ( [operation]
+      , [id]
+      , [number]
+      , [family_name]
+      , [first_name]
+      , [department]
+      , [subject]
+      , [entrance_date]
+      , [delete_flag])
+ SELECT 'U' AS operation
+      , id
+      , number
+      , family_name
+      , first_name
+      , department
+      , subject
+      , entrance_date
+      , delete_flag
+   FROM inserted
+ END
+ GO
+ ALTER TABLE [dbo].[m_student] ENABLE TRIGGER [update_m_student]
+ GO

DROP および CREATE のスクリプトを作成（デフォルト値：CREATE のスクリプトを作成）

「DROP および CREATE のスクリプトを作成」に変更して生成すると以下のようになります。

DROP および CREATE のスクリプトを作成

USE [excel_db]
GO
+ ALTER TABLE [dbo].[m_student] DROP CONSTRAINT [DF_m_student_delete_flag]
+ GO
+ /****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 23:08:38 ******/
+ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[m_student]') AND type in (N'U'))
+ DROP TABLE [dbo].[m_student]
+ GO
/****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 23:08:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_student](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NOT NULL,
    [family_name] [nchar](50) NOT NULL,
    [first_name] [nchar](50) NOT NULL,
    [department] [nchar](20) NOT NULL,
    [subject] [nchar](20) NOT NULL,
    [entrance_date] [date] NOT NULL,
    [delete_flag] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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 [dbo].[m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
GO

USE DATABASE のスクリプトを作成（デフォルト値：True）

USE DATABASE のスクリプトを作成を「False」に変更して生成すると以下のようになります。

USE DATABASE のスクリプトを作成：False

- USE [excel_db]
- GO
/****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 ******/23:19:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_student](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NOT NULL,
    [family_name] [nchar](50) NOT NULL,
    [first_name] [nchar](50) NOT NULL,
    [department] [nchar](20) NOT NULL,
    [subject] [nchar](20) NOT NULL,
    [entrance_date] [date] NOT NULL,
    [delete_flag] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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 [dbo].[m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
GO

スキーマ修飾オブジェクト名（デフォルト値：True）

スキーマ修飾オブジェクト名を「False」に変更して生成すると以下のようになります。

スキーマ修飾オブジェクト名：False

USE [excel_db]
GO
- /****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 23:19:58 ******/
+ /****** Object:  Table [m_student]    Script Date: 2025/02/11 23:20:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- CREATE TABLE [dbo].[m_student](
+ CREATE TABLE [m_student](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NOT NULL,
    [family_name] [nchar](50) NOT NULL,
    [first_name] [nchar](50) NOT NULL,
    [department] [nchar](20) NOT NULL,
    [subject] [nchar](20) NOT NULL,
    [entrance_date] [date] NOT NULL,
    [delete_flag] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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 [dbo].[m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
+ ALTER TABLE [m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
GO

説明用ヘッダーを含める（デフォルト値：True）

説明用ヘッダーを含めるを「False」に変更して生成すると以下のようになります。

説明用ヘッダーを含める：False

USE [excel_db]
GO
- /****** Object:  Table [dbo].[m_student]    Script Date: 2025/02/11 22:39:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_student](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [number] [int] NOT NULL,
    [family_name] [nchar](50) NOT NULL,
    [first_name] [nchar](50) NOT NULL,
    [department] [nchar](20) NOT NULL,
    [subject] [nchar](20) NOT NULL,
    [entrance_date] [date] NOT NULL,
    [delete_flag] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] 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 [dbo].[m_student] ADD  CONSTRAINT [DF_m_student_delete_flag]  DEFAULT ((0)) FOR [delete_flag]
GO

残課題

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

の記述については出力しない設定は見つかりませんでした。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up