-- 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];

 

-- CONSTRAINTTRIGGER를비활성화하는구문생성

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

Posted by Sting!
,