usar - Eliminar todos los datos en la base de datos de SQL Server

eliminar todo sql

Question

¿Cómo puedo eliminar todos los registros de todas las tablas de mi base de datos? ¿Puedo hacerlo con un comando SQL o necesito un comando SQL por cada tabla?

Answer 1

1. Primero tendrá que desactivar todos los desencadenantes:

   sp_msforeachtable ''ALTER TABLE ? DISABLE TRIGGER all'';

2. Ejecute este script: (Tomado de este post Gracias @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

Este script producirá declaraciones DELETE en el orden correcto. comenzando desde las tablas referenciadas y luego haciendo referencia a las

3. Copie las instrucciones DELETE FROM y ejecútelas una vez

4. habilitar disparadores

   sp_msforeachtable ''ALTER TABLE ? ENABLE TRIGGER all''

5. Confirmar los cambios:

   begin transaction
   commit;

Answer 2

Debajo de una secuencia de comandos que utilicé para eliminar todos los datos de una base de datos de 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
-- ....
-- ....
-- ....

Answer 3

Por lo general, es mucho más rápido escribir todos los objetos en la base de datos y crear uno vacío, que permite eliminar o truncar tablas.

Answer 4

Soy consciente de que esto es tarde, pero estoy de acuerdo con la sugerencia de AlexKuznetsov de escribir la base de datos, en lugar de pasar por la molestia de purgar los datos de las tablas. Si la solución TRUNCATE no funciona, y usted tiene una gran cantidad de datos, la emisión de sentencias DELETE (iniciadas) puede llevar mucho tiempo, y se le dejarán identificadores que no han sido resembrados (es decir, una INSERT en una tabla con una columna de IDENTITY obtendría una ID de 50000 en lugar de una ID de 1).

Para crear una base de datos completa, en SSMS, haga clic con el botón derecho en la base de datos, luego seleccione TASKS -> Generate scripts :

Haga clic en Next para omitir la pantalla de apertura del asistente, y luego seleccione los objetos que desea secuenciar:

En la pantalla Set scripting options , puede elegir configuraciones para las secuencias de comandos, como si se debe generar 1 secuencia de comandos para todos los objetos, o scripts separados para los objetos individuales, y si se debe guardar el archivo en Unicode o ANSI:

El asistente mostrará un resumen, que puede usar para verificar que todo sea como lo desee, y cierre haciendo clic en ''Finalizar''.

Answer 5

Usualmente usaré el proc no documentado 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

Ver también: Eliminar todos los datos en la base de datos (cuando tienes FK)

Answer 6

La solución de SQLMenace funcionó para mí con un ligero ajuste de cómo se borran los datos: DELETE FROM lugar de 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

Answer 7

/* 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

Answer 8

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