TSQL을 사용하여 데이터베이스의 모든 테이블을 잘라내려면 어떻게 해야 합니까?

TSQL을 사용하여 데이터베이스의 모든 테이블을 잘라내려면 어떻게 해야 합니까?

테스트 사이클 시작 시 새 데이터로 새로고침할 데이터베이스 테스트 환경이 있다.데이터베이스 전체의 재구축에는 관심이 없습니다.단순히 데이터를 「재설정」할 뿐입니다.

TSQL을 사용하여 모든 테이블에서 모든 데이터를 삭제하는 가장 좋은 방법은 무엇입니까?사용할 수 있는 시스템 저장 프로시저, 뷰 등이 있습니까?각 테이블에 대해 잘라낸 테이블 문을 수동으로 만들고 유지 관리하는 것이 아니라 동적이어야 합니다.

기본적으로 적절하게 설계된 데이터베이스와 마찬가지로 외부 키 관계가 있는 테이블에서 데이터를 삭제하는 경우 모든 제약을 해제하고 모든 데이터를 삭제한 후 다시 활성화할 수 있습니다.

-- disable all constraints
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

제약 조건 및 트리거 비활성화에 대한 자세한 내용은 여기를 참조하십시오.

테이블 중 일부에 ID 열이 있는 경우 다시 설치하고 싶을 수 있습니다.

EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"

REESED의 동작은 새로운 테이블과 이전에 BOL에서 일부 데이터를 삽입한 테이블에 따라 다릅니다.

DBCC CHECKIDENT('table_name', REESED, newReseedValue')

현재 ID 값은 newReseedValue로 설정됩니다.테이블 작성 후 테이블에 행을 삽입하지 않은 경우 DBCC CHECKIDENT 실행 후 삽입된 첫 번째 행은 newReseedValue를 ID로 사용합니다.그렇지 않으면 다음 행에 삽입된 newReseedValue + 1이 사용됩니다.newReseedValue 값이 ID 열의 최대값보다 작을 경우 테이블에 대한 후속 참조에서 오류 메시지 2627이 생성됩니다.

Robert가 제약조건을 비활성화하면 절단을 사용할 수 없다는 사실을 지적한 덕분에 제약조건을 삭제한 후 다시 작성해야 합니다.

SQL 2005의 경우

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

2000년 및 2005년/2008년 링크 추가..

여기 데이터베이스 삭제 스크립트의 킹대디가 있습니다.모든 테이블을 클리어하고 올바르게 다시 설치합니다.

SET QUOTED_IDENTIFIER ON;
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? DISABLE TRIGGER ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? CHECK CONSTRAINT ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? ENABLE TRIGGER ALL' 
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON';

IF NOT EXISTS (
    SELECT
        *
    FROM
        SYS.IDENTITY_COLUMNS
        JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE
        SYS.TABLES.Object_ID = OBJECT_ID('?') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL
)
AND OBJECTPROPERTY( OBJECT_ID('?'), 'TableHasIdentity' ) = 1

    DBCC CHECKIDENT ('?', RESEED, 0) WITH NO_INFOMSGS;

즐기세요, 하지만 조심하세요!

가장 간단한 방법은 하는 것이다.

1. SQL Management Studio 열기
2. 데이터베이스로 이동
3. 오른쪽 버튼을 클릭하여 [Tasks]-> [ Generate Scripts ](스크립트 생성)을 선택합니다(그림 1 ).
4. "개체 선택" 화면에서 "특정 개체 선택" 옵션을 선택하고 "테이블"을 선택합니다(그림 2).
5. 다음 화면에서 [Advanced]를 선택하고 [Script DROP and CREATE]옵션을 [Script DROP and CREATE](그림 3)로 변경합니다.
6. 스크립트를 새 편집기 창 또는 파일에 저장하고 필요에 따라 실행할 경우 선택합니다.

그러면 모든 테이블을 삭제 및 재생성할 수 있는 스크립트가 제공되므로 디버깅이나 모든 것이 포함되어 있는지 걱정할 필요가 없습니다.이는 단순히 잘라내는 것 이상의 작업을 수행하지만 결과는 동일합니다.자동 증가 기본 키는 마지막으로 할당된 값을 기억하는 잘린 테이블이 아니라 0에서 시작됩니다.또한 PreProd 또는 프로덕션 환경에서 Management Studio에 액세스할 수 없는 경우 코드에서 이 작업을 수행할 수도 있습니다.

1.

2.

3.

SQL Server에서는 외부 키를 사용하여 테이블을 잘라낼 수 없으므로 모든 테이블을 잘라내는 것은 테이블 간에 외부 키 관계가 없는 경우에만 작동합니다.

다른 방법으로 외부 키가 있는 테이블을 확인한 후 먼저 이 테이블에서 삭제한 후 외부 키를 사용하지 않고 테이블을 잘라낼 수 있습니다.

상세한 것에 대하여는, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 및 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 를 참조해 주세요.

이러지 마!정말, 좋은 생각이 아니야.

잘라낼 테이블을 알고 있는 경우 테이블을 잘라내는 저장 프로시저를 만듭니다.순서를 수정하면, 외부 키의 문제를 회피할 수 있습니다.

모든 것을 잘라내고 싶은 경우(예를 들어 BCP 로딩이 가능), 데이터베이스를 신속하게 폐기하고 새로운 데이터베이스를 작성할 수 있습니다.이것에 의해, 현재의 위치를 정확하게 알 수 있는 이점이 더해집니다.

MSQL Server Deveploper 또는 Enterprise에서 사용하는 대체 옵션은 빈 스키마를 작성한 후 바로 데이터베이스의 스냅샷을 작성하는 것입니다.이 시점에서 데이터베이스를 스냅샷으로 복원하기만 하면 됩니다.

특정 테이블(즉, 정적 룩업 테이블)에 데이터를 보관하고 동일한 DB 내의 다른 테이블에 있는 데이터를 삭제/트렁킹하려면 해당 테이블에 예외가 있는 루프가 필요합니다.이게 바로 제가 찾던 질문입니다.

sp_MSForEachTable은 버그가 있는 것 같습니다(즉, IF 문과 일치하지 않는 동작).그 때문에 MS에 의해 문서화되어 있지 않을 가능성이 있습니다.

declare @LastObjectID int = 0
declare @TableName nvarchar(100) = ''
set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
while(@LastObjectID is not null)
begin
    set @TableName = (select top 1 [name] from sys.tables where [object_id] = @LastObjectID)

    if(@TableName not in ('Profiles', 'ClientDetails', 'Addresses', 'AgentDetails', 'ChainCodes', 'VendorDetails'))
    begin
        exec('truncate table [' + @TableName + ']')
    end 

    set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
end

모든 테이블을 잘라낼 때 가장 어려운 것은 외부 키 제약 조건을 삭제하고 다시 추가하는 것입니다.

다음 쿼리는 @myTempTable의 각 테이블 이름과 관련된 제약조건별로 drop&create 문을 만듭니다.모든 테이블에 대해 이러한 이름을 생성하려면 정보 스키마를 사용하여 이러한 테이블 이름을 수집할 수 있습니다.

DECLARE @myTempTable TABLE (tableName varchar(200))
INSERT INTO @myTempTable(tableName) VALUES
('TABLE_ONE'),
('TABLE_TWO'),
('TABLE_THREE')


-- DROP FK Contraints
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
  '.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name) 
  FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
  WHERE fk.referenced_object_id IN 
      (
         SELECT so.object_id 
         FROM sys.objects so JOIN sys.schemas sc
         ON so.schema_id = sc.schema_id
         WHERE so.name IN (SELECT * FROM @myTempTable)  AND sc.name=N'dbo'  AND type in (N'U'))


 -- CREATE FK Contraints
 SELECT 'ALTER TABLE [PIMSUser].[dbo].[' +cast(c.name as varchar(255)) + '] WITH NOCHECK ADD CONSTRAINT ['+ cast(f.name as varchar(255)) +'] FOREIGN KEY (['+ cast(fc.name as varchar(255)) +'])
      REFERENCES [PIMSUser].[dbo].['+ cast(p.name as varchar(255)) +'] (['+cast(rc.name as varchar(255))+'])'
FROM  sysobjects f
      INNER JOIN sys.sysobjects c ON f.parent_obj = c.id
      INNER JOIN sys.sysreferences r ON f.id = r.constid
      INNER JOIN sys.sysobjects p ON r.rkeyid = p.id
      INNER JOIN sys.syscolumns rc ON r.rkeyid = rc.id and r.rkey1 = rc.colid
      INNER JOIN sys.syscolumns fc ON r.fkeyid = fc.id and r.fkey1 = fc.colid
WHERE 
      f.type = 'F'
      AND
      cast(p.name as varchar(255)) IN (SELECT * FROM @myTempTable)

그런 다음 실행할 문을 복사하기만 하면 됩니다. 단, 약간의 개발 작업만 하면 커서를 사용하여 동적으로 실행할 수 있습니다.

데이터베이스를 스크립팅한 후 드롭하여 스크립트에서 작성하는 것이 훨씬 쉬워집니다(또한 더 빠를 수도 있습니다).

빈 "템플릿" 데이터베이스를 만들고 전체 백업을 수행합니다.새로 고침이 필요한 경우 WITH REPLACE를 사용하여 복원하기만 하면 됩니다.빠르고, 단순하고, 방탄입니다.여기에 있는 테이블 몇 개 또는 여기에 기본 데이터(예: 구성 정보 또는 앱을 실행하기 위한 기본 정보)가 필요한 경우에도 이 기능을 처리합니다.

이렇게 하는 것도 한 방법인데...더 나은/효율적인 10개가 더 있을 것 같은데, 이 작업은 매우 드물게 이루어지는 것 같기 때문에...

리스트를 입수하다tables부터sysobjects다음으로 커서가 있는 것을 루프하여 호출합니다.sp_execsql('truncate table ' + @table_name)각각에 대해서iteration.

주석 처리된 섹션을 한 번 실행하고 _TruncateList 테이블에 잘라낼 테이블을 채운 다음 나머지 스크립트를 실행합니다.이 작업을 자주 수행하면 _ScriptLog 테이블을 시간이 지남에 따라 청소해야 합니다.

모든 테이블을 변경할 경우 SELECT name INTO #TruncateList FROM sys.tables 를 입력하면 됩니다.하지만, 여러분은 보통 그것들을 다 하고 싶지 않습니다.

또, 이것은 데이터베이스의 모든 외부 키에 영향을 줍니다.어플리케이션에 대해서 너무 무뚝뚝한 경우에도 변경할 수 있습니다.그건 내 목적이 아니야.

/*
CREATE TABLE _ScriptLog 
(
    ID Int NOT NULL Identity(1,1)
    , DateAdded DateTime2 NOT NULL DEFAULT GetDate()
    , Script NVarChar(4000) NOT NULL
)

CREATE UNIQUE CLUSTERED INDEX IX_ScriptLog_DateAdded_ID_U_C ON _ScriptLog
(
    DateAdded
    , ID
)

CREATE TABLE _TruncateList
(
    TableName SysName PRIMARY KEY
)
*/
IF OBJECT_ID('TempDB..#DropFK') IS NOT NULL BEGIN
    DROP TABLE #DropFK
END

IF OBJECT_ID('TempDB..#TruncateList') IS NOT NULL BEGIN
    DROP TABLE #TruncateList
END

IF OBJECT_ID('TempDB..#CreateFK') IS NOT NULL BEGIN
    DROP TABLE #CreateFK
END

SELECT Scripts = 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']'
INTO #DropFK
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

SELECT TableName
INTO #TruncateList
FROM _TruncateList

SELECT Scripts = 'ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
'
INTO #CreateFK
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO _ScriptLog (Script)
SELECT Scripts
FROM #CreateFK

DECLARE @Cmd NVarChar(4000)
    , @TableName SysName

WHILE 0 < (SELECT Count(1) FROM #DropFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #DropFK

    EXEC (@Cmd)

    DELETE #DropFK WHERE Scripts = @Cmd
END

WHILE 0 < (SELECT Count(1) FROM #TruncateList) BEGIN
    SELECT TOP 1 @Cmd = N'TRUNCATE TABLE ' +  TableName
        , @TableName = TableName
    FROM #TruncateList

    EXEC (@Cmd)

    DELETE #TruncateList WHERE TableName = @TableName
END

WHILE 0 < (SELECT Count(1) FROM #CreateFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #CreateFK

    EXEC (@Cmd)

    DELETE #CreateFK WHERE Scripts = @Cmd
END

조금 늦었지만 누군가에게 도움이 될지도 몰라요.T-SQL을 사용하여 다음 작업을 수행하는 프로시저를 만들기도 했습니다.

1. 모든 제약 조건을 임시 테이블에 저장
2. 모든 제약 조건 삭제
3. 잘라낼 필요가 없는 일부 테이블을 제외한 모든 테이블을 잘라냅니다.
4. 모든 구속조건을 다시 작성합니다.

여기 블로그에 올려놨어요.

각 테이블을 삭제하고 다시 작성하는 스크립트보다 데이터를 클리어하는 것이 더 좋은 이유를 알 수 없습니다.

또는 빈 DB를 백업하여 이전 DB에 복원하거나

테이블을 잘라내기 전에 모든 외부 키를 제거해야 합니다.이 스크립트를 사용하여 데이터베이스의 모든 외부 키를 삭제 및 재생성하는 최종 스크립트를 생성합니다.@action 변수를 'CREATE' 또는 'DROP'로 설정하십시오.

+탭에서 '삭제'를 선택합니다.INFORMATION_SCHEMA의 LE_NAME.TABLE_TYPE='BASE TABLE'이 있는 표

결과가 나오는 곳.

쿼리 창에 복사하여 붙여넣기하고 명령을 실행합니다.

언급URL : https://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql