source

SQL Server 데이터베이스의 모든 데이터 삭제

factcode 2023. 4. 9. 22:25
반응형

SQL Server 데이터베이스의 모든 데이터 삭제

데이터베이스의 모든 테이블에서 모든 레코드를 삭제하려면 어떻게 해야 합니까?1개의 SQL 명령어로 실행할 수 있습니까?또는 1개의 테이블당1개의 SQL 명령어가 필요합니까?

SQLMenace의 솔루션은 데이터 삭제 방법을 약간 수정하여 작동했습니다.DELETE FROM대신TRUNCATE.

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

보통 문서화되어 있지 않은 proc sp_MSForEachTable만 사용합니다.

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

다음 항목도 참조하십시오.데이터베이스의 모든 데이터 삭제(FK가 있는 경우)

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

늦은 감이 있지만 테이블에서 데이터를 삭제하는 번거로움을 겪지 말고 데이터베이스를 스크립팅하라는 AlexKuznetsov의 제안에 동의합니다.이 경우,TRUNCATE솔루션이 작동하지 않고 대량의 데이터를 가지고 있습니다.DELETE문장은 시간이 오래 걸리고 재시딩되지 않은 식별자(예:INSERT표로 진술하다IDENTITY컬럼에는 1의 ID가 아닌 50000의 ID가 표시됩니다.

전체 데이터베이스를 스크립팅하려면 SSMS에서 데이터베이스를 마우스 오른쪽 버튼으로 클릭한 후TASKS->Generate scripts:

여기에 이미지 설명 입력

클릭Next마법사 열기 화면을 건너뛰고 스크립팅할 개체를 선택하려면:

여기에 이미지 설명 입력

에서Set scripting options화면에서는 스크립트 설정을 선택할 수 있습니다.예를 들어 모든 오브젝트에 대해1개의 스크립트를 생성할지, 개별 오브젝트에 대해 개별 스크립트를 생성할지, 파일을 Unicode 또는 ANSI로 저장할지 등입니다.

여기에 이미지 설명 입력

마법사에 요약이 표시됩니다. 이 요약을 사용하여 모든 것이 원하는지 확인한 후 '마침'을 클릭하여 닫습니다.

  1. 먼저 모든 트리거를 비활성화해야 합니다.

    sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

  2. 이 스크립트를 실행합니다: (이 투고에서 취득합니다.@SQ 감사합니다.LMenace)

    SET NOCOUNT ON
GO

SELECT 'USE [' + db_name() +']';
;WITH a AS 
(
     SELECT 0 AS lvl, 
            t.object_id AS tblID 
     FROM sys.TABLES t
     WHERE t.is_ms_shipped = 0
       AND t.object_id NOT IN (SELECT f.referenced_object_id 
                               FROM sys.foreign_keys f)

     UNION ALL

     SELECT a.lvl + 1 AS lvl, 
            f.referenced_object_id AS tblId
     FROM a
     INNER JOIN sys.foreign_keys f ON a.tblId = f.parent_object_id 
                                   AND a.tblID <> f.referenced_object_id
)
SELECT 
    'Delete from ['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']' 
FROM a
GROUP BY tblId 
ORDER BY MAX(lvl),1

이 스크립트는DELETE올바른 순서로 진술합니다.참조된 표에서 시작하여 참조하는 표

  1. 복사하다DELETE FROM스테이트먼트를1회실행합니다.

  2. 트리거 활성화

    sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

  3. 변경을 커밋합니다.

    begin transaction
commit;

일반적으로 데이터베이스의 모든 개체를 스크립팅하고 빈 개체를 생성하여 테이블을 삭제하거나 잘라내는 것이 훨씬 빠릅니다.

SQL Server 데이터베이스에서 모든 데이터를 삭제하는 데 사용한 스크립트 아래

------------------------------------------------------------
/* Use database */ 
-------------------------------------------------------------

use somedatabase;

GO

------------------------------------------------------------------
/* Script to delete an repopulate the base [init database] */
------------------------------------------------------------------

-------------------------------------------------------------
/* Procedure delete all constraints */ 
-------------------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllConstraints
GO

CREATE PROCEDURE sp_DeleteAllConstraints
AS
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
GO

-----------------------------------------------------
/* Procedure delete all data from the database */ 
-----------------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_DeleteAllData' AND type = 'P')
    DROP PROCEDURE dbo.sp_DeleteAllData
GO

CREATE PROCEDURE sp_DeleteAllData
AS
    EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

-----------------------------------------------
/* Procedure enable all constraints */ 
-----------------------------------------------

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'sp_EnableAllConstraints' AND type = 'P')
    DROP PROCEDURE dbo.sp_EnableAllConstraints
GO
-- ....
-- ....
-- ....

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'

EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'

GO

대안으로 Visual Studio SSDT 또는 Red Gate SQL Compare를 사용하는 경우 스키마 비교를 실행하여 스크립트를 작성하고 오래된 데이터베이스를 삭제한 다음(데이터가 필요한 경우 먼저 백업을 작성) 비교 도구에서 생성한 스크립트를 사용하여 새 데이터베이스를 생성할 수 있습니다.매우 작은 데이터베이스에서는 이것이 더 많은 작업이 될 수 있지만, 매우 큰 데이터베이스에서는 단순히 데이터베이스를 삭제한 후 데이터베이스에 있을 수 있는 다양한 트리거 및 제약사항을 처리하는 것이 훨씬 더 빠릅니다.

기존 답변의 모든 훌륭한 팁과 몇 가지 더 조합하여 이 스크립트를 작성했습니다.좀 더 포괄적이고 훨씬 더 효율적일 것입니다.

삭제 단계 전에 스크립트는 다음과 같습니다.

  • 트리거를 비활성화합니다(삭제 시 트리거가 있는 경우 특히 중요함).
  • 제약 조건 체크를 비활성화합니다.
  • non-timeout 인덱스를 디세블로 합니다(테이블과 함께 행별로 삭제됩니다).

삭제 절차에서는 가능한 한 빨리 잘라내거나 탭 잠금 힌트를 사용하여 삭제합니다(탭잠금은 힙 테이블에만 도움이 되며 파일을 축소하므로 여기에 아무것도 추가하지 않을 수 있습니다).

삭제 절차 후 스크립트는 다음을 수행합니다.

  • 모든 인덱스 재구성/활성화(비어 있음)
  • 제약 조건 검사 사용
  • 트리거 활성화
  • ID 열 다시 시드
  • 데이터베이스 파일을 축소하다

데이터베이스에서 모든 데이터 삭제:

use [your_database]
go

exec sp_MSforeachtable 'alter table ? disable trigger all'
go

exec sp_MSforeachtable 'alter table ? nocheck constraint all'
go

-- Disable enabled nonclustered indices
declare @script nvarchar(max)
declare cr cursor fast_forward read_only for
select 'alter index ' + quotename(i.name) + ' on ' + quotename(schema_name(t.schema_id))+'.'+ quotename(t.name) + ' disable'
from sys.indexes i inner join sys.tables t on i.object_id = t.object_id
where i.type_desc = 'nonclustered' and i.name is not null and i.is_disabled = 0;
open cr
fetch next from cr into @script
while @@fetch_status = 0
begin
    execute sp_executesql @script 
    fetch next from cr into @script
end
close cr
deallocate cr
go

exec sp_MSforeachtable 'set quoted_identifier on; if objectproperty(object_id(''?''), ''TableHasForeignRef'') = 1 delete from ? with (tablock) else truncate table ?'
go

exec sp_MSforeachtable 'set quoted_identifier on; alter index all on ? rebuild';
go

exec sp_MSforeachtable 'alter table ? with check check constraint all'
go

exec sp_MSforeachtable 'alter table ? enable trigger all'
go

-- Re-seed identity columns
exec sp_MSforeachtable 'if objectproperty(object_id(''?''), ''TableHasIdentity'') = 1 dbcc checkident(''?'', reseed, 0)'
go

-- Shrink the database files
declare @db_name nvarchar(200) = db_name()
dbcc shrinkdatabase (@db_name, 0);  
go

시간과 공간을 절약하고 가능하면 DELETE 대신 TRUNCATE를 사용하면 대용량 데이터베이스가 있는 경우 로그 파일이 부풀어 오르지 않습니다.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable '
        IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
            DELETE FROM ?
        ELSE
            TRUNCATE TABLE ?'

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

EXEC sys.sp_msforeachable 'ALTER TABLE ? NOCHECK RESTRANS ALL' EXEC sys.sp_msforeable 'DELETE FROM?'

EXEC sp_MSForEachTable '따옴표 붙임_IDENTIFIER ON; DELETE FROM?'

EXEC sys.sp_MSForEachTable '변경표 ? 제약조건 모두 확인'

예, 코드 한 줄로 삭제할 수 있습니다.

SELECT 'TRUNCATE TABLE ' + d.NAME + ';' 
FROM   sys.tables d 
WHERE  type = 'U'

언급URL : https://stackoverflow.com/questions/3687575/delete-all-data-in-sql-server-database

반응형

'source' 카테고리의 다른 글

IDisposable을 사용하여 Excel Interop 객체 정리  (0) 2023.04.09
Excel VBA - 루프 종료  (0) 2023.04.09
「Microsoft.ACE.OLEDB.12.0' 공급자가 로컬 시스템에 등록되어 있지 않습니다." xlsx 프로세스를 SQL 서버로 가져오는 동안 오류가 발생했습니다.  (0) 2023.04.09
리치 텍스트 상자 wpf 바인딩  (0) 2023.04.09
각도 재료 설계에서 도구 설명에 선 구분을 추가하는 방법  (0) 2023.04.04

'source'의 다른글

  • 현재글SQL Server 데이터베이스의 모든 데이터 삭제

관련글

댓글

티스토리툴바