[sql-server-2005] SQL Server 데이터베이스의 모든 테이블에서 모든 행을 삭제하는 방법은 무엇입니까?

해결책은 다음과 같습니다.

1. 제약 조건을 삭제합니다 ( 이 게시물 덕분에 )
2. INFORMATION_SCHEMA.TABLES특정 데이터베이스에 대해 반복
3. SELECTS 일부 검색 기준에 따른 테이블
4. 해당 테이블에서 모든 데이터를 삭제합니다
5. 구속 조건 다시 추가
6. 같은 특정 테이블의 무시를 허용 sysdiagrams하고__RefactorLog

처음에 시도 EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?'했지만 다이어그램이 삭제되었습니다.

USE <DB name>;
GO

-- Disable all constraints in the database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

declare @catalog nvarchar(250);
declare @schema nvarchar(250);
declare @tbl nvarchar(250);
DECLARE i CURSOR LOCAL FAST_FORWARD FOR select
                                        TABLE_CATALOG,
                                        TABLE_SCHEMA,
                                        TABLE_NAME
                                        from INFORMATION_SCHEMA.TABLES
                                        where
                                        TABLE_TYPE = 'BASE TABLE'
                                        AND TABLE_NAME != 'sysdiagrams'
                                        AND TABLE_NAME != '__RefactorLog'

OPEN i;
FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM [' + @catalog + '].[' + @schema + '].[' + @tbl + '];'
        /* Make sure these are the commands you want to execute before executing */
        PRINT 'Executing statement: ' + @sql
        -- EXECUTE sp_executesql @sql
        FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
    END
CLOSE i;
DEALLOCATE i;

-- Re-enable all constraints again
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

답변

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

Set nocount on

Exec sp_MSForEachTable 'Alter Table ? NoCheck Constraint All'

Exec sp_MSForEachTable
'
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1
Begin
-- Just to know what all table used delete syntax.
Print ''Delete from '' + ''?''
Delete From ?
End
Else
Begin
-- Just to know what all table used Truncate syntax.
Print ''Truncate Table '' + ''?''
Truncate Table ?
End
'

Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'