データベース内の全テーブルの全レコードを削除する

データベース内の全テーブルの全レコードを削除するには？

データベース内の全テーブルの全レコードを削除したいような時ありませんか？

今回はそんな時に使えるスクリプトをご紹介します。


スクリプトはいきなり実行せず、データベースのコピーを作ってテストをした後、しっかりバックアップをとってから実行してください！


ドキュメントされていない sp_MSForEachTable を使う方法

sp_MSForEachTable はマイクロソフトによってドキュメントされていないストアドプロシージャで、引数で指定したコマンドを全てのユーザーテーブルについて実行してくれます。

これを利用して、全テーブルのデータを削除してみましょう。


手順は以下の通りです。

  1. 全ユーザーテーブルの全てのトリガーを無効化
  2. 全ユーザーテーブルの FOREIGN KEYと CHECK 制約を無効化
  3. 全ユーザーテーブルからデータを削除
  4. 全ユーザーテーブルの FOREIGN KEYと CHECK 制約を有効化
  5. 全ユーザーテーブルの全てのトリガーを有効化
  6. IDENTITY をリシード（0 にリセット） - オプショナル

ユーザーテーブルの全てのトリガーと FOREIGN KEYと CHECK 制約を一度無効化して、データを削除後に有効化します。

以下のスクリプトなどで、既に無効化されていて、有効化されては困るものがないか確認してください。 必要に応じて、スクリプト実行後に再度無効化してください。 

SELECT * FROM sys.triggers WHERE is_disabled = 1;
SELECT * FROM sys.foreign_keys WHERE is_disabled = 1;
SELECT * FROM sys.check_constraints WHERE is_disabled = 1;

[ 実行結果 ]
データベース内の全テーブルの全レコードを削除する 1


sp_MSForEachTable を利用して全ユーザーテーブルの全データを削除するスクリプトは次の通りです。 

SET XACT_ABORT ON;
BEGIN TRANSACTION;

-- 1. 全ユーザーテーブルの全てのトリガーを無効化
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL;'

-- 2. 全ユーザーテーブルの FOREIGN KEYと CHECK 制約を無効化
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL;'

-- 3. 全ユーザーテーブルからデータを削除
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?;'

-- 4. 全ユーザーテーブルの FOREIGN KEYと CHECK 制約を有効化
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL;'

-- 5. 全ユーザーテーブルの全てのトリガーを有効化
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL;'

-------------------------------------------------
-- 6. IDENTITY をリシード（0 にリセット）
EXEC sp_MSForEachTable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 
			   DBCC CHECKIDENT(''?'', RESEED, 0);'

-------------------------------------------------
-- 7. 結果の確認
EXEC sp_MSForEachTable 'SELECT COUNT(*) FROM ?;'

COMMIT TRANSACTION;

[ 実行結果 ]
データベース内の全テーブルの全レコードを削除する 2

一応、トランザクションに入れていますが、成功するまで実行すれば、何度実行しても大丈夫ですし、必要がなければ入れなくて良いと思います。

外部キー制約で参照されているテーブルは、レコードがなくても TRUNCATE TABLE でデータを削除することができないので、 DELETE ステートメントを使ってデータを削除しています。

「 次の SET オプションには不適切な設定 'QUOTED_IDENTIFIER' があります 」 というエラーが出ることがあるので、DELETE の前に SET QUOTED_IDENTIFIER ON; に設定しています。

IDENTITY のリシードはおまけです。 IDENTITY カラムがあるテーブルのみに対して実行しています。


sp_MSForEachTable はドキュメントされていないストアードプロシージャなので、スクリプトをマニュアルで実行する際にのみ使用してください。

くれぐれも間違って実行してしまわないように気をつけてくださいね！

© 2010-2024 SQL Server 入門