[SQL Server]AdventureWorksからデータが空のファイルを簡単に作りたい。

Question

SQL Server 初心者です。

質問なのですが、

Visual StudioもしくはSQL Server Management Studio を使い、

公式で提供されているデータサンプル AdventureWorks.mdfから

テーブルのデータが空のmdfファイルを簡単に作成するにはどうすればよいでしょうか？

DELETE FROM テーブル名
GO

で削除することもできますがテーブルが多すぎて非効率ですし、
IDで連動しているテーブルは削除が面倒です。

他に良い方法はないでしょうか？

どなたかご教授ください

Answer 1

下記のSQLで全テーブルのデータ削除ができます。

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

こちらを参考にしました。
How do you truncate all tables in a database using TSQL?

Comments

[makiikeda1216]

mochaさん回答ありがとうございます！

USE [AdventureWorks2012]
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

というコマンドを実行しました。
その結果

[エラー]
FOREIGN KEY 制約でテーブル 'Production.ScrapReason' が参照されているので、このテーブルは切り捨てられません。

[参考サイト]

>>You cannot truncate tables which have foreign keys, so this will only work if th>>ere are no foreign key constraints between tables (or they have been disabled).
Foreign Key　制約がある場合はどうやら削除できないらしいですOTL

制約がある場合のコマンドも書いてありましたので
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
exec sp_MSforeachtable 'DELETE FROM ?'
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

もやってみましたが
[エラー]
DELETE が失敗しました。次の SET オプションには不適切な設定 'QUOTED_IDENTIFIER' があります。SET オプションが、インデックス付きビュー、計算列のインデックス、フィルター選択されたインデックス、クエリ通知、XML データ型のメソッド、空間インデックスの操作 で使用するのに適切であることを確認してください。

とでましたOTL

参考の１１番目くらいにあった
スクリプトの生成による削除をみつけたので実行しました
1.open up SQL Management Studio
2.navigate to your database
3.Right-click and select Tasks->Generate Scripts (pic 1)
4.On the "choose Objects" screen, select the "select specific objects" option and check "tables" (pic 2)
5.on the next screen, select "advanced" and then change the "Script DROP and CREATE" option to "Script DROP and CREATE" (pic 3)
6.Choose to save script to a new editor window or a file and run as necessary.

エラーメッセージが何個か表示されましたが
データの中身が消えてました！
とりあえずこれでデータ構造だけ取り出せるようです！
ありがとうございます！

ちなみにエラーメッセージはこんな感じでした
メッセージ 3729、レベル 16、状態 1、行 3613
DROP TABLE 'Production.ProductModelProductDescriptionCulture' できません。オブジェクト 'vProductAndDescription' によって参照されています。
メッセージ 3729、レベル 16、状態 1、行 3619
DROP TABLE 'Production.ProductModel' できません。オブジェクト 'vProductAndDescription' によって参照されています。
メッセージ 3729、レベル 16、状態 1、行 3631
DROP TABLE 'Production.ProductDescription' できません。オブジェクト 'vProductAndDescription' によって参照されています。
メッセージ 3729、レベル 16、状態 1、行 3640
DROP TABLE 'Production.Product' できません。オブジェクト 'vProductAndDescription' によって参照されています。
メッセージ 3729、レベル 16、状態 1、行 3658
DROP TABLE 'Person.StateProvince' できません。オブジェクト 'vStateProvinceCountryRegion' によって参照されています。
メッセージ 3729、レベル 16、状態 1、行 3676
DROP TABLE 'Person.CountryRegion' できません。オブジェクト 'vStateProvinceCountryRegion' によって参照されています。
メッセージ 2714、レベル 16、状態 6、行 4021
データベースに 'CountryRegion' という名前のオブジェクトが既に存在します。
メッセージ 2714、レベル 16、状態 6、行 4139
データベースに 'StateProvince' という名前のオブジェクトが既に存在します。
メッセージ 2714、レベル 16、状態 6、行 4267
データベースに 'Product' という名前のオブジェクトが既に存在します。
メッセージ 2714、レベル 16、状態 6、行 4341
データベースに 'ProductDescription' という名前のオブジェクトが既に存在します。
メッセージ 2714、レベル 16、状態 6、行 4415
データベースに 'ProductModel' という名前のオブジェクトが既に存在します。
メッセージ 2714、レベル 16、状態 6、行 4451
データベースに 'ProductModelProductDescriptionCulture' という名前のオブジェクトが既に存在します。

[makiikeda1216]

追記ですが

上のエラーの出ているテーブルはデータがのこったままになっていました。
ほかのテーブルは削除が成功していました。

[mocha]

>makiikeda1216 さん
DELETE文のところを、
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON;Delete From ?'
としたらどうでしょうか？

なお、Vendorsテーブルはトリガで削除できないようになっているようです。

[makiikeda1216]

>mochaさん

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON;Delete From ?'
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

としたところ、削除がすべてうまくいきました！
ありがとうございます！

Answer 2

新規のデータベースを作成し、サンプルのデータベースを削除すれば良いです。
逆でもOKですが、新規DB作成してからサンプルを削除した方が安全ですね。

作成手順のサイトです

削除はManagement Studioのデータベースフォルダ中のサンプルデータベースを右クリックして
メニューより削除できたはずです。

Comments

[makiikeda1216]

BlueMoonさん

回答ありがとうございます！

>>新規のデータベースを作成し、サンプルのデータベースを削除すれば良いです

説明不足だったようですみません。作成手順のサイトもみさせていただいたのですが
データ構造はそのまま残した空のデータベースを作成したいのです。

SQL Server Management Studioでその作業をためしにおこなった途中で

データベース右クリ→タスク→スクリプト生成

という作業でデータ構造だけをスクリプト化する方法をみつけたのですが
「これかな？」と思ったんですが、スキーマあたりでひっかかってしまったり
して半分くらいのテーブルができなかったりしてうまくいきませんね。。

[BlueMoon]

テーブル構造は残してということでしたね、失礼いたしました m(__)m
ラフな手段ですが、テーブルリストをエディタに張り付けてtruncate文として整形して一括実行する方法しか思いつかないですね。参照制約でひっかかるのは主にマスター系なのでトランザクション系のテーブルから先に削除すれば良いかと思います。
参照制約でエラーになった場合は、該当テーブルのtruncateを後にすれば良いと思います。

[makiikeda1216]

返信ありがとうございます！

>>。参照制約でひっかかるのは主にマスター系なのでトランザクション系のテーブルから先に削除すれば良いかと思います。
参照制約でエラーになった場合は、該当テーブルのtruncateを後にすれば良いと思います。

なるほど！参照制約エラーの該当テーブルはtrucateの順番によって変わってくるんですね！
試してみます！