모든 테이블의 데이터 일괄 삭제하기

프로젝트 초기에 개발 DB에서 자주 사용할 만한 쿼리일 듯 ^^

 

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

 

출처 : http://www.devx.com/dbzone/Article/40967

 

하지만, 그대로 사용하기엔 좀 부족해 보인다.

아래 사항을 추가해서 사용해야겠다.

1.     삭제하지 않을 테이블을 지정할 수 있을 것.

2.     DELETE문 대신 TRUNCATE TABLE문을 사용할 수 있다면… TRUNCATE TABLE문을 쓰도록 분기할 것. (테이블이 VIEW에 schema binding되어 있다면… DELETE문을 쓸 수 없을 듯)

3.     IDENTITY 속성의 컬럼을 포함한 경우, Reseed 할 것.
이때, 한번도 데이터가 INSERT되지 않았거나 TRUNCATE한 테이블과… 그렇지 않은 테이블의 Reseed 값을 달리 적용해야 함에 유의!!!

위의 조건 중 1번, 3번 항을 적용한 스크립트. ^^

delete_all.sql

소스 코드는 아래와 같습니다.

-- SQL Server 2005 이상에서사용할수있습니다.

-- DELETE문을사용하므로대용량테이블이존재한다면이스크립트는부적합할수있습니다.

-- Ctrl-Shift-M을눌러데이터베이스이름과삭제하지않을테이블을설정한후실행합니다.

 

USE <Database Name,sysname,>;

GO

 

SET NOCOUNT ON;

GO

 

DECLARE

    @nvcStmt nvarchar(max),

    @nvcIgnoreTables nvarchar(max);

 

DECLARE @tblTargetTables table (

    [object_id] int NOT NULL PRIMARY KEY,

    schemaName sysname NOT NULL,

    tableName sysname NOT NULL

);

 

DECLARE @tblIdentityTables table (

    tableName sysname NOT NULL,

    seed_value int NOT NULL,

    increment_value int NOT NULL,

    last_value int NULL

);

 

SET @nvcIgnoreTables = N'<삭제제외테이블을comma를구분자로나열,,>';

 

-- 삭제대상테이블정보를수집합니다.

INSERT @tblTargetTables ([object_id], schemaName, tableName)

SELECT T.[object_id], S.name, T.name

FROM sys.tables T

    INNER JOIN sys.schemas S ON T.[schema_id] = S.[schema_id]

WHERE T.type = 'U' AND NOT EXISTS (

    SELECT *

    FROM (

        SELECT LTRIM(RTRIM(SUBSTRING(

              N',' + @nvcIgnoreTables + N','

            , number + 1

            , CHARINDEX(N',', N',' + @nvcIgnoreTables + N',', number + 1) - number - 1

        ))) AS tableName

        FROM master.dbo.spt_values

        WHERE [type] = 'P' AND number < (DATALENGTH(N',' + @nvcIgnoreTables + N',') / 2)

            AND SUBSTRING(N',' + @nvcIgnoreTables + N',', number, 1) = N','

    ) S

    WHERE tableName = T.name

);

 

-- 삭제대상테이블의identity 속성컬럼정보를수집합니다.

INSERT @tblIdentityTables (tableName, seed_value, increment_value, last_value)

SELECT T.tableName, CAST(I.seed_value AS int), CAST(I.increment_value AS int)

    , CAST(I.last_value AS int)

FROM @tblTargetTables T

    INNER JOIN sys.identity_columns I ON T.[object_id] = I.[object_id];

 

-- CONSTRAINT와TRIGGER를비활성화하는구문생성

SET @nvcStmt = N'DISABLE TRIGGER ALL ON DATABASE;

EXEC sp_MSForEachTable ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'';

EXEC sp_MSForEachTable ''ALTER TABLE ? DISABLE TRIGGER ALL'';';

 

-- 테이블DELETE 구문생성

SELECT @nvcStmt = @nvcStmt + NCHAR(13) + NCHAR(10) +

    N'DELETE ' + QUOTENAME(schemaName) + N'.' + QUOTENAME(tableName) + N';'

FROM @tblTargetTables;

 

-- identity 속성의컬럼을초기화하는구문생성

SELECT @nvcStmt = @nvcStmt + NCHAR(13) + NCHAR(10) +

    N'DBCC CHECKIDENT(''' + tableName + ''', RESEED, ' +

    CASE

        WHEN last_value IS NULL THEN CAST(seed_value AS nvarchar(10))

        ELSE CAST(seed_value - increment_value AS nvarchar(10))

    END + N');'

FROM @tblIdentityTables;

 

SET @nvcStmt = @nvcStmt + '

EXEC sp_MSForEachTable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'';

EXEC sp_MSForEachTable ''ALTER TABLE ? ENABLE TRIGGER ALL'';

ENABLE TRIGGER ALL ON DATABASE;';

 

-- PRINT @nvcStmt;

 

EXEC sp_executesql @nvcStmt;