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

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

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

https://learn.microsoft.com/ja-jp/sql/ssms/menu-help/options-sql-server-object-explorer-scripting

# スクリプトの生成手順
１．データベースを右クリック
　　⇒ タスク
　　⇒ スクリプトの生成
　　を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/26e7ec2f-3c13-d7d9-41f7-595c723ac261.png)
２．何も考えず次へ進みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/96abd21b-aba8-bd27-b386-1e3cce63a21f.png)
３．出力するオブジェクトを選択して次へ進みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/195cf483-400f-2eea-fe44-087271791fbd.png)
４．出力方法を選択して次へ進みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/4e359b32-f997-8546-be3c-4af888a1e333.png)
５．設定を確認して次へ進みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/02aad0af-ed46-ffe1-95c8-cc9b6b596291.png)
６．スクリプトが生成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/4bd6b2c6-effd-4ca5-037d-1fb11a87995e.png)

# 詳細設定
デフォルトの設定は以下の通りです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/824ff30e-1066-3a6a-0067-81463744b9c1.png)


サンプルとして以下のようなテーブルのスクリプトを生成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/30a6c0c6-3197-6728-7114-9f02eb558af3.png)

### デフォルト設定
詳細設定を何も変更せずに生成すると以下のようになります。
```sql:デフォルト設定で生成
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」に変更して生成すると以下のようになります。
```diff_sql:インデックスのスクリプトを作成：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」に変更して生成すると以下のようになります。
```diff_sql:トリガーのスクリプトを作成：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 のスクリプトを作成」に変更して生成すると以下のようになります。
```diff_sql: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」に変更して生成すると以下のようになります。
```diff_sql: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」に変更して生成すると以下のようになります。
```diff_sql:スキーマ修飾オブジェクト名：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」に変更して生成すると以下のようになります。
```diff_sql:説明用ヘッダーを含める：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
```

### 残課題
```sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
```
の記述については出力しない設定は見つかりませんでした。