-- 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;
출처 : http://youngsam.kr/696
