database - vaciar - truncate varias tablas sql

¿BASE DE DATOS DE TRUNCATE SQL? Cómo truncar todas las tablas

Question

Yo uso SQL Server 2008 R2.

¿Hay un comando SQL para vaciar la base de datos, en lugar de tener que truncar las 20 tablas?

Solo quiero borrar los datos no la estructura.

Answer 1

La mayoría de las soluciones proporcionadas no utilizan TRUNCATE, que es diferente de DELETE, o no tratan el problema de las restricciones de clave externa. La solución provista por Chaitanya se acerca, pero recurre al uso de BORRAR, y lo hace en un procedimiento almacenado, que parece no encajar en una situación en la que está cargando los datos de una base de datos.

Por lo tanto, a continuación se muestra mi variación, que utiliza TRUNCATE y resuelve el problema de restricción de clave externa.

 /* TRUNCATE ALL TABLES IN A DATABASE */
DECLARE @dropAndCreateConstraintsTable TABLE
(
DropStmt VARCHAR(MAX)
,CreateStmt VARCHAR(MAX)
)
/* Gather information to drop and then recreate the current foreign key constraints */
INSERT @dropAndCreateConstraintsTable
SELECT DropStmt = ''ALTER TABLE ['' + ForeignKeys.ForeignTableSchema
+ ''].['' + ForeignKeys.ForeignTableName + ''] DROP CONSTRAINT [''
+ ForeignKeys.ForeignKeyName + '']; ''
,CreateStmt = ''ALTER TABLE ['' + ForeignKeys.ForeignTableSchema
+ ''].['' + ForeignKeys.ForeignTableName
+ ''] WITH CHECK ADD CONSTRAINT ['' + ForeignKeys.ForeignKeyName
+ ''] FOREIGN KEY(['' + ForeignKeys.ForeignTableColumn
+ '']) REFERENCES ['' + SCHEMA_NAME(sys.objects.schema_id)
+ ''].['' + sys.objects.[name] + ''](['' + sys.columns.[name]
+ '']); ''
FROM sys.objects
INNER JOIN sys.columns
ON ( sys.columns.[object_id] = sys.objects.[object_id] )
INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
,sys.objects.[name] AS ForeignTableName
,sys.columns.[name] AS ForeignTableColumn
,sys.foreign_keys.referenced_object_id AS referenced_object_id
,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
FROM sys.foreign_keys
INNER JOIN sys.foreign_key_columns
ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
INNER JOIN sys.objects
ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
INNER JOIN sys.columns
ON ( sys.columns.[object_id] = sys.objects.[object_id] )
AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
) ForeignKeys
ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
WHERE ( sys.objects.[type] = ''U'' )
AND ( sys.objects.[name] NOT IN ( ''sysdiagrams'' ) )
/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT */
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT DropStmt
FROM @dropAndCreateConstraintsTable
OPEN C1
FETCH NEXT FROM C1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''Executing '' + @DropStatement
EXECUTE sp_executesql @DropStatement
FETCH NEXT FROM C1 INTO @DropStatement
END
CLOSE C1
DEALLOCATE C1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE C2 CURSOR READ_ONLY
FOR
SELECT ''TRUNCATE TABLE [dbo].['' + TABLE_NAME + '']''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ''dbo''
AND TABLE_TYPE = ''BASE TABLE''
/* Change your schema appropriately if you don''t want to use dbo */
OPEN C2
FETCH NEXT FROM C2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''Executing '' + @DeleteTableStatement
EXECUTE sp_executesql @DeleteTableStatement
FETCH NEXT FROM C2 INTO @DeleteTableStatement
END
CLOSE C2
DEALLOCATE C2
/* Recreate foreign key constraints */
DECLARE C3 CURSOR READ_ONLY
FOR
SELECT CreateStmt
FROM @dropAndCreateConstraintsTable
OPEN C3
FETCH NEXT FROM C3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''Executing '' + @RecreateStatement
EXECUTE sp_executesql @RecreateStatement
FETCH NEXT FROM C3 INTO @RecreateStatement
END
CLOSE C3
DEALLOCATE C3
GO

Answer 2

La respuesta aceptada no funciona cuando tienes relaciones de clave externa. En ese caso, tienes que eliminar las restricciones y volver a crearlas. A continuación se encuentra un proceso almacenado para hacer eso basado en la respuesta here

CREATE PROCEDURE [dbo].[deleteAllDataFromAllTables] AS
SET NOCOUNT ON
DECLARE @dropAndCreateConstraintsTable TABLE ( DropStmt varchar(max) , CreateStmt varchar(max) )
insert @dropAndCreateConstraintsTable select
DropStmt = ''ALTER TABLE ['' + ForeignKeys.ForeignTableSchema +
''].['' + ForeignKeys.ForeignTableName +
''] DROP CONSTRAINT ['' + ForeignKeys.ForeignKeyName + '']; ''
, CreateStmt = ''ALTER TABLE ['' + ForeignKeys.ForeignTableSchema +
''].['' + ForeignKeys.ForeignTableName +
''] WITH CHECK ADD CONSTRAINT ['' + ForeignKeys.ForeignKeyName +
''] FOREIGN KEY(['' + ForeignKeys.ForeignTableColumn +
'']) REFERENCES ['' + schema_name(sys.objects.schema_id) + ''].['' +
sys.objects.[name] + ''](['' +
sys.columns.[name] + '']); ''
from sys.objects
inner join sys.columns
on (sys.columns.[object_id] = sys.objects.[object_id])
inner join (
select sys.foreign_keys.[name] as ForeignKeyName
,schema_name(sys.objects.schema_id) as ForeignTableSchema
,sys.objects.[name] as ForeignTableName
,sys.columns.[name] as ForeignTableColumn
,sys.foreign_keys.referenced_object_id as referenced_object_id
,sys.foreign_key_columns.referenced_column_id as referenced_column_id
from sys.foreign_keys
inner join sys.foreign_key_columns
on (sys.foreign_key_columns.constraint_object_id
= sys.foreign_keys.[object_id])
inner join sys.objects
on (sys.objects.[object_id]
= sys.foreign_keys.parent_object_id)
inner join sys.columns
on (sys.columns.[object_id]
= sys.objects.[object_id])
and (sys.columns.column_id
= sys.foreign_key_columns.parent_column_id)
) ForeignKeys
on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
and (ForeignKeys.referenced_column_id = sys.columns.column_id)
where (sys.objects.[type] = ''U'')
and (sys.objects.[name] not in (''sysdiagrams''))
DECLARE @DropStatement nvarchar(max)
DECLARE @RecreateStatement nvarchar(max)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT DropStmt from @dropAndCreateConstraintsTable
OPEN C1
FETCH NEXT FROM C1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''Executing '' + @DropStatement
execute sp_executesql @DropStatement
FETCH NEXT FROM C1 INTO @DropStatement
END
CLOSE C1
DEALLOCATE C1
DECLARE @DeleteTableStatement nvarchar(max)
DECLARE C2 CURSOR READ_ONLY
FOR
SELECT ''Delete From [dbo].['' + TABLE_NAME + '']'' from INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ''dbo'' -- Change your schema appropriately.
OPEN C2
FETCH NEXT FROM C2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''Executing '' + @DeleteTableStatement
execute sp_executesql @DeleteTableStatement
FETCH NEXT FROM C2 INTO @DeleteTableStatement
END
CLOSE C2
DEALLOCATE C2
DECLARE C3 CURSOR READ_ONLY
FOR
SELECT CreateStmt from @dropAndCreateConstraintsTable
OPEN C3
FETCH NEXT FROM C3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''Executing '' + @RecreateStatement
execute sp_executesql @RecreateStatement
FETCH NEXT FROM C3 INTO @RecreateStatement
END
CLOSE C3
DEALLOCATE C3
GO

Answer 3

No hay script sin sentido esto:

EXEC sp_MSForEachTable ''ALTER TABLE ? NOCHECK CONSTRAINT ALL''
EXEC sp_MSForEachTable ''DELETE FROM ?''
EXEC sp_MSForEachTable ''ALTER TABLE ? CHECK CONSTRAINT ALL''
EXEC sp_MSforeachtable ''DBCC CHECKIDENT ( ''''?'''', RESEED, 0)''
GO

Truncar aún no funcionará si tiene claves externas en las tablas. Este script también restablecerá todas las columnas de identidad.

Answer 4

Puede utilizar el procedimiento almacenado sp_MSforeachtable de la siguiente manera:

USE MyDatabase
EXEC sp_MSforeachtable ''TRUNCATE TABLE ?''

Tenga en cuenta que esto eliminará (por truncamiento) TODOS los datos de todas las tablas de usuarios. Y en caso de que no pueda TRUNCATE debido a claves foráneas, etc., puede ejecutar lo mismo que eliminar:

USE MyDatabase
EXEC sp_MSforeachtable ''DELETE FROM ?''

Answer 5

También puede usar este procedimiento almacenado si solo desea truncar todas las tablas en un esquema específico:

-- =============================================
-- Author: Ben de Ridder
-- Create date: 20160513
-- Description: Truncate all tables in schema
-- =============================================
CREATE PROCEDURE TruncateAllTablesInSchema
@schema nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table nVARCHAR(255)
DECLARE db_cursor CURSOR FOR
select t.name
from sys.tables t inner join
sys.schemas s on
t.schema_id=s.schema_id
where s.name=@schema
order by 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
declare @sql nvarchar(1000)
set @sql = ''truncate table ['' + @schema + ''].['' + @table + '']''
exec sp_sqlexec @sql
FETCH NEXT FROM db_cursor INTO @table
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
GO

Answer 6

Tomando un punto de las respuestas de Boycs Answer y mtmurdock, tengo el siguiente procedimiento almacenado en todas mis bases de datos de desarrollo o de prueba. He agregado algunos modificadores para satisfacer mis propios requisitos si necesito agregar declaraciones para reiniciar los datos de ciertas columnas.

( Nota: habría agregado esto como un comentario a la brillante respuesta de Boycs, pero aún no tengo la reputación de hacerlo. Por favor, acepte mis disculpas por agregar esto como una respuesta completamente nueva).

ALTER PROCEDURE up_ResetEntireDatabase
@IncludeIdentReseed BIT,
@IncludeDataReseed BIT
AS
EXEC sp_MSForEachTable ''ALTER TABLE ? NOCHECK CONSTRAINT ALL''
EXEC sp_MSForEachTable ''DELETE FROM ?''
IF @IncludeIdentReseed = 1
BEGIN
EXEC sp_MSForEachTable ''DBCC CHECKIDENT (''''?'''' , RESEED, 1)''
END
EXEC sp_MSForEachTable ''ALTER TABLE ? CHECK CONSTRAINT ALL''
IF @IncludeDataReseed = 1
BEGIN
-- Populate Core Data Table Here
END
GO

Y luego, una vez lista, la ejecución es realmente simple:

EXEC up_ResetEntireDatabase 1, 1

Answer 7

Yo uso este script

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

Answer 8

ejecuta esto

EXEC sp_MSforeachtable ''PRINT ''''ALTER TABLE ? NOCHECK CONSTRAINT ALL''''''
EXEC sp_MSforeachtable ''print ''''DELETE FROM ?''''''
EXEC sp_MSforeachtable ''print ''''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all''''''

Después de copiar el resultado impreso, péguelo en el campo Consulta y ejecútelo. Truncará todas las tablas.

Answer 9

DECLARE @nombre NVARCHAR(100)
DECLARE @tablas TABLE(nombre nvarchar(100))
INSERT INTO @tablas
SELECT t.TABLE_SCHEMA+ ''.''+t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T
DECLARE @contador INT=0
SELECT @contador=COUNT(*) FROM INFORMATION_SCHEMA.TABLES
WHILE @contador>0
BEGIN
SELECT TOP 1 @nombre=nombre FROM @tablas
DECLARE @sql NVARCHAR(500)=''''
SET @sql =@sql+''Truncate table ''+@nombre
EXEC (@sql)
SELECT @sql
SET @contador=@contador-1
DELETE TOP (1) @tablas
END