DONGBUM KIM

Beat Yesterday

3 분 소요

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

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

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

2026년 수정 안내 처음 올렸던 프로시저에 문제가 여럿 있었다. 뷰까지 TRUNCATE 대상에 포함되고, FK로 참조되는 테이블에서 실패하며, @@ERROR 기반 오류 처리가 동작하지 않았다. 본문 설명에도 MSSQL이 아닌 MySQL 용어(AUTO_INCREMENT)를 썼다. 전부 바로잡은 버전으로 교체한다.

TRUNCATE의 제약부터

자동화하기 전에 TRUNCATE TABLE의 제약을 알아야 한다.

  • 다른 테이블이 FK로 참조하는 테이블은 TRUNCATE할 수 없다. DELETE는 되지만 TRUNCATE는 안 된다. 실무 스키마에서는 거의 반드시 걸린다.
  • 인덱싱된 뷰가 걸린 테이블, 복제에 참여하는 테이블도 불가능하다.
  • TRUNCATE는 DELETE와 달리 IDENTITY 시드를 초기값으로 되돌린다. (MySQL의 AUTO_INCREMENT에 해당하는 MSSQL 개념이 IDENTITY다.)
  • 개별 행 삭제를 로깅하지 않아 DELETE보다 훨씬 빠르고 로그를 덜 쓴다.

FK 문제 때문에, 실무에서는 FK를 잠시 끄고 → TRUNCATE 대신 DELETE → 시드 리셋 순서로 가거나, FK가 걸린 테이블만 DELETE로 처리한다. 아래 프로시저는 후자를 택했다.

프로시저

USE [GameDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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

AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON            -- 오류 발생 시 트랜잭션 전체를 확실히 롤백한다
	SET LOCK_TIMEOUT 30000

	SET @o_result = -1000

	-- 실수 방지용 안전 장치
	IF @confirm <> N'truncate_confirm'
	BEGIN
		SET @o_result = -1001
		RETURN
	END

	BEGIN TRY
		BEGIN TRAN

		DECLARE @TEMP_TABLE TABLE ( seq INT IDENTITY, sql_text NVARCHAR(1024) )

		INSERT	@TEMP_TABLE ( sql_text )
				SELECT	CASE
							-- FK로 참조되는 테이블은 TRUNCATE가 불가능하므로 DELETE로 처리
							WHEN EXISTS (
								SELECT 1
								FROM   sys.foreign_keys AS fk
								WHERE  fk.referenced_object_id = t.object_id
							)
							THEN CONCAT( 'DELETE FROM ', QUOTENAME( s.name ), '.', QUOTENAME( t.name ), ';' )
							ELSE CONCAT( 'TRUNCATE TABLE ', QUOTENAME( s.name ), '.', QUOTENAME( t.name ), ';' )
						END
				FROM	sys.tables AS t                        -- 뷰는 포함되지 않는다
				JOIN	sys.schemas AS s ON s.schema_id = t.schema_id
				WHERE	t.is_ms_shipped = 0
				AND		t.temporal_type <> 1                   -- 시스템 버전 히스토리 테이블 제외

		DECLARE @i INT = 1, @j INT = ( SELECT COUNT(*) FROM @TEMP_TABLE )
		DECLARE @sql NVARCHAR(1024)

		WHILE @i <= @j
		BEGIN
			SELECT @sql = sql_text FROM @TEMP_TABLE WHERE seq = @i
			EXEC sp_executesql @sql

			SET @i = @i + 1
		END

		-- 추가로 비우고 싶은 다른 DB의 테이블은 여기에 적는다
		TRUNCATE TABLE [AccountDB].[dbo].[OtherTable]

		COMMIT TRAN
		SET @o_result = 0
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN

		SET @o_result = ERROR_NUMBER()

		-- 호출한 쪽이 실패를 알 수 있도록 다시 던진다
		THROW
	END CATCH
END
GO

원래 코드에서 고친 것

1. 뷰가 섞여 들어갔다

information_schema.tables는 뷰(VIEW)도 함께 반환한다. 뷰에 TRUNCATE를 걸면 실패한다. sys.tables를 쓰거나 WHERE TABLE_TYPE = 'BASE TABLE' 조건을 넣어야 한다.

2. FK 참조 테이블에서 실패했다

위에서 설명한 대로 sys.foreign_keys를 확인해 참조되는 테이블은 DELETE로 돌렸다.

3. @@ERROR로는 오류를 잡을 수 없었다

@@ERROR는 직전 한 문장의 결과만 담는다. 원래 코드는 WHILE 루프가 다 끝나고 다른 문장까지 실행한 뒤에 IF @@ERROR = 0을 검사했으므로, 루프 안에서 난 오류는 이미 사라진 뒤였다.

게다가 ELSE 절의 SET @o_result = @@ERROR는 항상 0이 들어간다. IF @@ERROR = 0이라는 비교 문장 자체가 실행되면서 @@ERROR를 0으로 덮어쓰기 때문이다. 오류 처리는 TRY...CATCH와 ERROR_NUMBER()로 해야 한다.

4. 조기 반환 시 OUTPUT이 NULL이었다

@confirm이 틀리면 @o_result를 설정하기도 전에 RETURN해서, 호출한 쪽은 NULL을 받았다. 실패 코드를 먼저 넣고 반환하도록 바꿨다.

5. 문자열 결합에 QUOTENAME을 씌웠다

동적 SQL로 테이블 이름을 조립할 때는 QUOTENAME으로 감싸야 한다. 이름에 공백이나 예약어가 들어간 경우에 깨지지 않는다.

6. 불필요한 격리 수준 지정을 뺐다

원래 있던 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED는 데이터를 지우는 프로시저에 아무 의미가 없어 제거하고, 대신 오류 시 확실히 롤백되도록 SET XACT_ABORT ON을 넣었다.

사용

DECLARE @result INT
EXEC [dbo].[GSP_GD_TABLE_TRUNCATE] N'truncate_confirm', @result OUTPUT
SELECT @result

운영 DB에서는 절대 실행하지 말 것. 이름 그대로 모든 테이블을 비운다. 개발 DB 전용으로만 배포하고, 가능하면 운영 서버에는 아예 생성하지 않는 편이 안전하다.

댓글 남기기

참고