MSSQL에서 여러 테이블 데이터 한 번에 삭제하기

MSSQL 개발 중에는 DB를 초기화해야 하는 경우가 종종 생긴다. 잘못된 데이터가 들어가거나, 코드 변경에 따라 DB 구조가 달라지면서 오류가 발생하기 때문이다.

DB를 아예 삭제하고 다시 생성하면 깔끔하지만, 그러면 테이블과 프로시저를 모두 다시 생성해야 한다. 원하는 것은 테이블 구조는 그대로 두고 데이터만 비우는 것이었다.

DELETE 쿼리로 테이블을 하나씩 비울 수도 있지만, 테이블 수가 늘어날수록 손이 너무 많이 간다.

자동화할 방법을 고민하다가 다음과 같은 프로시저를 작성했다.

USE [GameDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GSP_GD_TABLE_TRUNCATE]
	@confirm			NVARCHAR(1024)
,	@o_result			INT		OUTPUT

AS

	SET NOCOUNT ON
	SET LOCK_TIMEOUT 30000
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	IF @confirm != N'truncate_confirm'
		RETURN

	SET @o_result = -1000;

	BEGIN TRAN
		DECLARE @TEMP_TABLE TABLE( seq int identity, table_name NVARCHAR(1024) )

		INSERT	@TEMP_TABLE
				SELECT	CONCAT('TRUNCATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
				FROM	information_schema.tables
				WHERE	TABLE_CATALOG = 'GameDB'

		DECLARE @i int, @j int
		SELECT @i = 1, @j = @@ROWCOUNT

		WHILE @i <= @j
		BEGIN
			DECLARE @sql NVARCHAR(1024) = (SELECT table_name FROM @TEMP_TABLE WHERE seq = @i)
			EXEC( @sql )

			SET @i = @i + 1
		END

    -- 추가로 삭제하고 싶은 테이블들은 여기에 별도로 추가
		TRUNCATE TABLE [AccountDB].[dbo].[OtherTable]

	IF @@ERROR = 0
	BEGIN
		COMMIT TRAN
		SET @o_result = 0;
	END
	ELSE
	BEGIN
		ROLLBACK TRAN
		SET @o_result = @@ERROR
	END

GO

프로시저를 실행할 때 인자에 truncate_confirm을 입력해야 한다. 이는 실수로 실행하는 것을 방지하기 위한 안전 장치다.

GameDB 외의 다른 데이터베이스 테이블을 추가로 비우고 싶다면 OtherTable 부분에 계속 추가하면 된다.

프로시저를 실행하면 GameDB 내 모든 테이블 목록을 자동으로 조회하고 TRUNCATE를 수행한다. TRUNCATE는 DELETE와 달리 AUTO_INCREMENT 값도 초기화된다.

개발 DB 초기화 요청이 들어오면 프로시저 한 번 실행으로 끝낼 수 있다.