迷ったらコレ！TSQLでデータベースの全テーブルを安全に消去する3つのステップ

TSQLを使用してデータベース内のすべてのテーブルを簡単に切り捨てることができます。テーブルを切り捨てることは、テスト環境をリセットしたり、古いデータを削除したりする必要がある場合に役立ちます。

方法

データベース内のすべてのテーブルを切り捨てるには、次の2つの方法があります。

TRUNCATE TABLE ステートメントは、テーブル内のすべてのデータを迅速かつ効率的に削除するために使用できます。このステートメントは、テーブル構造と制約を維持します。

TRUNCATE TABLE [schema_name].[table_name];

例：

AdventureWorks2019 データベース内の Person.Address テーブルを切り捨てるには、次のステートメントを使用します。

TRUNCATE TABLE Person.Address;

DELETE ステートメントは、テーブルから特定の条件に基づいてデータを削除するために使用できます。すべてのデータを削除するには、WHERE 句を省略します。

DELETE FROM [schema_name].[table_name];

DELETE FROM Person.Address;

注意事項

• TRUNCATE TABLE ステートメントは、ロールバックできない操作です。削除前にデータをバックアップすることを強くお勧めします。
• DELETE ステートメントは、TRUNCATE TABLE ステートメントよりも時間がかかる場合があります。これは、DELETE ステートメントが各行を個別に処理するためです。

• SSMS (SQL Server Management Studio) を使用して、テーブルを右クリックし、[Truncate Table] を選択することもできます。
• T-SQL スクリプトを使用して、TRUNCATE TABLE ステートメントを実行することもできます。

補足

• 上記の方法は、すべてのテーブルデータを削除します。特定のテーブルのデータを削除したい場合は、WHERE 句を使用して条件を指定する必要があります。
• TRUNCATE TABLE ステートメントは、IDENTITY 列の値をリセットします。DELETE ステートメントは、IDENTITY 列の値をリセットしません。

-- AdventureWorks2019 データベース内のすべてのテーブルを切り捨てる

DECLARE @schema_name VARCHAR(50)
DECLARE @table_name VARCHAR(128)

SET @schema_name = 'dbo'

CURSOR csr_tables
FOR SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
AND schema_name = @schema_name

OPEN csr_tables

FETCH NEXT FROM csr_tables INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_truncate_table @schema_name + '.' + @table_name
    FETCH NEXT FROM csr_tables INTO @table_name
END

CLOSE csr_tables
DEALLOCATE csr_tables

DELETE ステートメントを使用する

-- AdventureWorks2019 データベース内のすべてのテーブルからすべてのデータを削除する

DECLARE @schema_name VARCHAR(50)
DECLARE @table_name VARCHAR(128)

SET @schema_name = 'dbo'

CURSOR csr_tables
FOR SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
AND schema_name = @schema_name

OPEN csr_tables

FETCH NEXT FROM csr_tables INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_delete_table @schema_name + '.' + @table_name
    FETCH NEXT FROM csr_tables INTO @table_name
END

CLOSE csr_tables
DEALLOCATE csr_tables

説明

上記のサンプルコードは、AdventureWorks2019 データベース内のすべてのテーブルを切り捨てる方法を示しています。

• 最初のサンプルコードは、TRUNCATE TABLE ステートメントを使用してすべてのテーブルを切り捨てます。
• 2番目のサンプルコードは、DELETE ステートメントを使用してすべてのテーブルからすべてのデータを削除します。

データベース内のすべてのテーブルを切り捨てるその他の方法

SSMS を使用して、データベース内のすべてのテーブルを切り捨てることができます。

手順

1. SSMS を起動し、データベースに接続します。
2. オブジェクト エクスプローラーで、データベースを展開します。
3. テーブル フォルダーを展開します。
4. すべてのテーブルを選択します。
5. 右クリックして、Truncate Table を選択します。

確認メッセージが表示されます。

T-SQL スクリプトを使用する

TRUNCATE TABLE ステートメントまたは DELETE ステートメントを使用して、すべてのテーブルを切り捨てる T-SQL スクリプトを作成できます。

-- TRUNCATE TABLE ステートメントを使用する

DECLARE @schema_name VARCHAR(50)
DECLARE @table_name VARCHAR(128)

SET @schema_name = 'dbo'

CURSOR csr_tables
FOR SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
AND schema_name = @schema_name

OPEN csr_tables

FETCH NEXT FROM csr_tables INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_truncate_table @schema_name + '.' + @table_name
    FETCH NEXT FROM csr_tables INTO @table_name
END

CLOSE csr_tables
DEALLOCATE csr_tables

-- DELETE ステートメントを使用する

DECLARE @schema_name VARCHAR(50)
DECLARE @table_name VARCHAR(128)

SET @schema_name = 'dbo'

CURSOR csr_tables
FOR SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
AND schema_name = @schema_name

OPEN csr_tables

FETCH NEXT FROM csr_tables INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_delete_table @schema_name + '.' + @table_name
    FETCH NEXT FROM csr_tables INTO @table_name
END

CLOSE csr_tables
DEALLOCATE csr_tables

1. データベース エンジン ツールを起動します。
2. データベース タブを選択します。
3. 接続するデータベースを選択します。
4. 以下のクエリを実行します。

TRUNCATE TABLE [schema_name].[table_name];

または

DELETE FROM [schema_name].[table_name];