Eliminar todos los datos en la base de datos SQL Server
¿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 tabla?
8 answers
La solución de SQLMenace funcionó para mí con un ligero ajuste a cómo se eliminan los datos - DELETE FROM
en 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-28 22:27:38
Normalmente solo usaré el proc indocumentado 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 se tiene FKs)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-09-10 19: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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-02-21 08:42:11
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 resulta que tiene una gran cantidad de datos, emitir sentencias DELETE
(registradas) podría tomar mucho tiempo, y se le quedarán identificadores que no se han vuelto a sembrar (es decir, una sentencia INSERT
en una tabla con una columna IDENTITY
le obtendría un ID de 50000 en lugar de un ID de 1).
Para escribir una base de datos completa, en SSMS, haga clic con el botón derecho en la base de datos y luego seleccione TASKS
-> Generate scripts
:
Haga clic en Next
para omitir la pantalla de apertura del asistente y, a continuación, seleccione qué objetos desea escribir:
En la pantalla Set scripting options
, puede elegir la configuración para el scripting, como si generar 1 script para todos los objetos, o scripts separados para los objetos individuales, y si guardar el archivo en Unicode o ANSI:
El asistente mostrará un resumen, que puede usar para verificar que todo esté como desee, y cerrar haciendo clic en 'Finalizar'.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-02-21 09:03:22
Normalmente es mucho más rápido eliminar todos los objetos de la base de datos, y crear uno vacío, que eliminar o truncar tablas.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-09-10 19:34:38
-
Primero tendrás que desactivar todos los disparadores:
sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';
-
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á instrucciones DELETE
en el orden correcto. a partir de tablas referenciadas y luego haciendo referencia a las
-
Copie las instrucciones
DELETE FROM
y ejecútelas una vez -
Activar disparadores
sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
-
Confirme los cambios :
begin transaction commit;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-12-03 17:01:53
Debajo de un script 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
-- ....
-- ....
-- ....
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-03-25 12: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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-02-01 14:06:54