개발이나 테스트 과정을 반복하다 보면 데이터베이스의 모든 테이블을 삭제하고 초기화해야 할 때가 있습니다. 하지만 테이블이 수십 개가 넘어가면 일일이 DROP TABLE을 입력하기란 쉽지 않죠.
오늘은 MSSQL(SQL Server)에서 시스템 프로시저를 이용하는 간편한 방법부터, 최신 버전에서 권장하는 카탈로그 뷰 활용 방법까지 정리해 보겠습니다.
⚠️ 주의사항 (필독)
이 작업은 실행 즉시 데이터와 구조가 영구적으로 삭제됩니다. 복구가 불가능하므로, 운영 서버가 아닌 반드시 테스트 환경에서만 사용하시기 바랍니다. 실행 전 백업 여부를 반드시 확인하세요!
1. 시스템 프로시저 sp_MSforeachtable 사용하기
Microsoft에서 공식적으로 지원 문서를 제공하지는 않지만, 오랫동안 널리 쓰여온 미등록(Undocumented) 프로시저입니다. 현재 데이터베이스의 모든 테이블을 순회하며 명령어를 실행합니다.
-- 현재 데이터베이스의 모든 테이블 삭제
EXEC sp_MSforeachtable 'DROP TABLE ?'
- 장점: 한 줄로 끝나서 매우 간편합니다.
- 단점: 외래 키(Foreign Key) 제약 조건이 있는 경우, 참조 순서 때문에 한 번에 삭제되지 않을 수 있습니다. 이 경우 제약 조건을 먼저 제거하거나 여러 번 실행해야 합니다.
2. 카탈로그 뷰(sys.objects)를 이용한 스크립트 생성 (권장)
최신 SQL Server 버전에서는 시스템 뷰를 조회하여 DROP 구문을 문자열로 생성하는 방식을 더 권장합니다. 외래 키나 스키마 정보를 명확히 확인할 수 있어 안전합니다.
① 테이블 삭제 스크립트 생성
SELECT 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';' AS TableToDrop
FROM sys.objects
WHERE type = 'U'; -- 'U'는 사용자 테이블(User Table)을 의미
- QUOTENAME 사용: 테이블 명에 공백이나 특수문자가 섞여 있어도 안전하게 처리합니다.
- 결과 복사: 위 쿼리의 실행 결과(Text)를 전체 복사하여 새 쿼리창에서 실행하면 됩니다.
② 기타 객체(뷰, 프로시저, 함수) 삭제 스크립트
테이블 외에도 다른 객체들을 정리하고 싶을 때 아래 쿼리를 사용하세요.
-- VIEW 삭제
SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';' FROM sys.objects WHERE type = 'V';
-- STORED PROCEDURE 삭제
SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';' FROM sys.objects WHERE type = 'P';
-- FUNCTION 삭제
SELECT 'DROP FUNCTION ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';' FROM sys.objects WHERE type IN ('FN', 'IF', 'TF');
3. [보너스] 모든 데이터베이스 관리 (시스템 관리자용)
데이터베이스 전체 목록을 확인하거나 점검할 때 유용한 명령어들입니다.
-- 모든 데이터베이스 목록 출력
EXEC sp_MSforeachdb 'PRINT "?"'
-- 모든 데이터베이스 무결성 체크 (DBCC CHECKDB)
EXEC sp_MSforeachdb 'DBCC CHECKDB (?)'
-- (극도로 주의!) 모든 데이터베이스 삭제
-- EXEC sp_MSforeachdb 'DROP DATABASE [?]'
마치며
과거에는 단순히 테이블 이름만 연결해서 삭제하곤 했지만, 최신 환경에서는 스키마(Schema)와 객체 이름의 보호(QUOTENAME)를 고려하는 것이 중요합니다.
특히 외래 키 제약 조건이 얽혀 있는 경우에는 제약 조건을 먼저 드롭하는 스크립트를 생성하거나, 데이터베이스 자체를 삭제 후 재생성하는 것이 더 깔끔할 수 있습니다. 항상 실행 전 SELECT 문으로 대상 리스트를 먼저 확인하는 습관을 들이세요!
Copyright 2026. [Ryon] all rights reserved.