Удалить все данные в базе данных SQL Server

Как я могу удалить все записи из всех таблиц в моей базе? Могу ли я сделать это с помощью одной команды SQL или мне нужна одна команда SQL на одну таблицу?

691   8  

8 ответов:

решение 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
150  
2015-01-29 00:27:38

обычно я просто использую недокументированный 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

Читайте также: удалить все данные в базе данных (когда у вас есть FKs)

35  
2010-09-10 22:13:09 
/* 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
14  
2013-02-21 10:42:11

Я знаю, что это поздно, но я согласен с предложением Алексея Кузнецова написать сценарий базы данных, а не проходить через хлопоты по очистке данных из таблиц. Если TRUNCATE решение не будет работать, и у вас будет большой объем данных, выдающих (регистрируемых)DELETE заявления может занять много времени, и вы останетесь с идентификаторами, которые не были повторно заполнены (т. е. INSERT оператор в таблицу с IDENTITY столбец даст вам идентификатор 50000 вместо идентификатора на 1).

чтобы создать сценарий для всей базы данных, в SSMS щелкните правой кнопкой мыши базу данных и выберите TASKS ->Generate scripts:

enter image description here

клик Next чтобы пропустить экран открытия мастера, а затем выбрать объекты, которые вы хотите создать сценарий:

enter image description here

на Set scripting options экран, вы можете выбрать настройки для сценариев, например, следует ли создать 1 сценарий для всех объектов или отдельные сценарии для отдельных объектов, и следует ли сохранить файл в Юникоде или ANSI:

enter image description here

мастер покажет резюме, которое вы можете использовать, чтобы убедиться, что все по желанию, и закрыть, нажав на кнопку "Готово".

12  
2013-02-21 11:03:22

обычно гораздо быстрее выписать все объекты в базе данных и создать пустой, который нужно удалить из или усечь таблицы.

5  
2010-09-10 22:34:38

  1. сначала вам придется отключить все триггеры : 

    sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

  2. запустите этот скрипт : (взято из этого в должности спасибо @SQLMenace) 

    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 операторы и запустить их один раз

  2. включить триггеры 

    sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

  3. зафиксировать изменения : 

    begin transaction
commit;
4  
2014-12-03 19:01:53

ниже сценария, который я использовал для удаления всех данных из базы данных 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
-- ....
-- ....
-- ....
3  
2017-03-25 14:03:40 
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
0  
2016-02-01 16:06:54

Comments

    Ничего не найдено.
Click here to cancel reply.