sql-server - examples - transact sql mysql

¿Cómo se truncan todas las tablas en una base de datos usando TSQL?

Question

Tengo un entorno de prueba para una base de datos que quiero volver a cargar con nuevos datos al comienzo de un ciclo de prueba. No estoy interesado en reconstruir toda la base de datos, simplemente "restableciendo" los datos.

¿Cuál es la mejor manera de eliminar todos los datos de todas las tablas con TSQL? ¿Hay procedimientos, vistas, etc. almacenados en el sistema que se puedan usar? No quiero crear y mantener manualmente declaraciones de tabla truncadas para cada tabla; preferiría que fuera dinámico.

Answer 1

Antes de truncar las tablas, debe eliminar todas las claves foráneas. Utilice esta script de script para generar scripts finales para descartar y volver a crear todas las claves externas en la base de datos. Establezca la variable @action en ''CREAR'' o ''GOTA''.

Answer 2

Aquí está el rey papi de las secuencias de comandos de limpieza de bases de datos. Borrará todas las tablas y las resembrará correctamente:

SET QUOTED_IDENTIFIER ON;
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON; ALTER TABLE ? NOCHECK CONSTRAINT ALL''  
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON; ALTER TABLE ? DISABLE TRIGGER ALL''  
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON; DELETE FROM ?''  
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON; ALTER TABLE ? CHECK CONSTRAINT ALL''  
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON; ALTER TABLE ? ENABLE TRIGGER ALL'' 
EXEC sp_MSforeachtable ''SET QUOTED_IDENTIFIER ON;

IF NOT EXISTS (
    SELECT
        *
    FROM
        SYS.IDENTITY_COLUMNS
        JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE
        SYS.TABLES.Object_ID = OBJECT_ID(''''?'''') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL
)
AND OBJECTPROPERTY( OBJECT_ID(''''?''''), ''''TableHasIdentity'''' ) = 1

    DBCC CHECKIDENT (''''?'''', RESEED, 0) WITH NO_INFOMSGS''

Disfruta, pero ten cuidado!

Answer 3

Cuando se trata de eliminar datos de tablas que tienen relaciones de claves externas, que es básicamente el caso con cualquier base de datos diseñada correctamente, podemos deshabilitar todas las restricciones, eliminar todos los datos y luego volver a habilitar las restricciones

-- disable all constraints
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Más sobre desactivar restricciones y disparadores here

si algunas de las tablas tienen columnas de identidad, podemos resembrarlas

EXEC sp_MSForEachTable "DBCC CHECKIDENT ( ''?'', RESEED, 0)"

Tenga en cuenta que el comportamiento de RESEED difiere entre la nueva tabla y la que ha tenido algunos datos insertados previamente desde BOL :

DBCC CHECKIDENT (''table_name'', RESEED, newReseedValue)

El valor de identidad actual se establece en newReseedValue. Si no se han insertado filas en la tabla desde que se creó, la primera fila insertada después de ejecutar DBCC CHECKIDENT usará newReseedValue como identidad. De lo contrario, la siguiente fila insertada usará newReseedValue + 1. Si el valor de newReseedValue es menor que el valor máximo en la columna de identidad, se generará el mensaje de error 2627 en referencias posteriores a la tabla.

Gracias a Robert por señalar el hecho de que la inhabilitación de restricciones no permite utilizar truncado, las restricciones tendrían que descartarse, y luego volver a crearse

Answer 4

Ejecute la sección comentada una vez, llene la tabla _TruncateList con las tablas que desea truncar, luego ejecute el resto del script. La tabla _ScriptLog tendrá que limpiarse con el tiempo si haces esto mucho.

Puede modificar esto si quiere hacer todas las tablas, simplemente ponga SELECCIONAR el nombre INTO #TruncateList FROM sys.tables. Sin embargo, generalmente no desea hacerlos todos.

Además, esto afectará todas las claves externas en la base de datos, y usted puede modificar eso también si es demasiado contundente para su aplicación. No es para mis propósitos.

/*
CREATE TABLE _ScriptLog 
(
    ID Int NOT NULL Identity(1,1)
    , DateAdded DateTime2 NOT NULL DEFAULT GetDate()
    , Script NVarChar(4000) NOT NULL
)

CREATE UNIQUE CLUSTERED INDEX IX_ScriptLog_DateAdded_ID_U_C ON _ScriptLog
(
    DateAdded
    , ID
)

CREATE TABLE _TruncateList
(
    TableName SysName PRIMARY KEY
)
*/
IF OBJECT_ID(''TempDB..#DropFK'') IS NOT NULL BEGIN
    DROP TABLE #DropFK
END

IF OBJECT_ID(''TempDB..#TruncateList'') IS NOT NULL BEGIN
    DROP TABLE #TruncateList
END

IF OBJECT_ID(''TempDB..#CreateFK'') IS NOT NULL BEGIN
    DROP TABLE #CreateFK
END

SELECT Scripts = ''ALTER TABLE '' + ''['' + OBJECT_NAME(f.parent_object_id)+ '']''+
'' DROP  CONSTRAINT '' + ''['' + f.name  + '']''
INTO #DropFK
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

SELECT TableName
INTO #TruncateList
FROM _TruncateList

SELECT Scripts = ''ALTER TABLE '' + const.parent_obj + ''
    ADD CONSTRAINT '' + const.const_name + '' FOREIGN KEY (
            '' + const.parent_col_csv + ''
            ) REFERENCES '' + const.ref_obj + ''('' + const.ref_col_csv + '')
''
INTO #CreateFK
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + ''.'' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT '','' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('''')
                ), 1, 1, '''') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + ''.'' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT '','' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('''')
                ), 1, 1, '''') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO _ScriptLog (Script)
SELECT Scripts
FROM #CreateFK

DECLARE @Cmd NVarChar(4000)
    , @TableName SysName

WHILE 0 < (SELECT Count(1) FROM #DropFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #DropFK

    EXEC (@Cmd)

    DELETE #DropFK WHERE Scripts = @Cmd
END

WHILE 0 < (SELECT Count(1) FROM #TruncateList) BEGIN
    SELECT TOP 1 @Cmd = N''TRUNCATE TABLE '' +  TableName
        , @TableName = TableName
    FROM #TruncateList

    EXEC (@Cmd)

    DELETE #TruncateList WHERE TableName = @TableName
END

WHILE 0 < (SELECT Count(1) FROM #CreateFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #CreateFK

    EXEC (@Cmd)

    DELETE #CreateFK WHERE Scripts = @Cmd
END

Answer 5

Es mucho más fácil (y posiblemente incluso más rápido) crear una secuencia de comandos de su base de datos, luego simplemente suelte y cree desde el script.

Answer 6

Esta es una forma de hacerlo ... probablemente hay otras 10 que son mejores / más eficientes, pero parece que esto se hace con poca frecuencia, así que aquí va ...

obtener una lista de las tables de sysobjects , luego recorrer las que tienen un cursor, llamando a sp_execsql(''truncate table '' + @table_name) para cada iteration .

Answer 7

Haga una base de datos vacía de la "plantilla", tome una copia de seguridad completa. Cuando necesite actualizar, simplemente restaure usando WITH REPLACE. Rápido, simple, a prueba de balas. Y si un par de tablas aquí o allá necesita algunos datos básicos (por ejemplo, información de configuración, o simplemente información básica que hace que su aplicación se ejecute) también maneja eso.

Answer 8

La forma más simple de hacerlo es

1. abrir SQL Management Studio
2. navega a tu base de datos
3. Haga clic con el botón derecho y seleccione Tareas-> Generar guiones (foto 1)
4. En la pantalla "elegir objetos", seleccione la opción "seleccionar objetos específicos" y marque "tablas" (foto 2)
5. en la pantalla siguiente, seleccione "Avanzado" y luego cambie la opción "Caída de guiones y CREACIÓN" a "Crear guiones y CREAR" (foto 3)
6. Elija guardar script en una nueva ventana del editor o un archivo y ejecútelo si es necesario.

esto le dará una secuencia de comandos que descarta y recrea todas sus tablas sin la necesidad de preocuparse por la eliminación de errores o si ha incluido todo. Mientras esto realiza más que solo un truncado, los resultados son los mismos. Solo tenga en cuenta que sus claves primarias de incremento automático comenzarán en 0, a diferencia de las tablas truncadas que recordarán el último valor asignado. También puede ejecutar esto desde el código si no tiene acceso a Management Studio en sus entornos PreProd o Production.

1.

2.

3.

Answer 9

La parte más difícil de truncar todas las tablas es eliminar y volver a aplicar las restricciones de clave externa.

La siguiente consulta crea las instrucciones drop & create para cada restricción relacionada con cada nombre de tabla en @myTempTable. Si desea generar estos para todas las tablas, puede usar el esquema de información para reunir estos nombres de tabla.

DECLARE @myTempTable TABLE (tableName varchar(200))
INSERT INTO @myTempTable(tableName) VALUES
(''TABLE_ONE''),
(''TABLE_TWO''),
(''TABLE_THREE'')


-- DROP FK Contraints
SELECT ''alter table ''+quotename(schema_name(ob.schema_id))+
  ''.''+quotename(object_name(ob.object_id))+ '' drop constraint '' + quotename(fk.name) 
  FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
  WHERE fk.referenced_object_id IN 
      (
         SELECT so.object_id 
         FROM sys.objects so JOIN sys.schemas sc
         ON so.schema_id = sc.schema_id
         WHERE so.name IN (SELECT * FROM @myTempTable)  AND sc.name=N''dbo''  AND type in (N''U''))


 -- CREATE FK Contraints
 SELECT ''ALTER TABLE [PIMSUser].[dbo].['' +cast(c.name as varchar(255)) + ''] WITH NOCHECK ADD CONSTRAINT [''+ cast(f.name as varchar(255)) +''] FOREIGN KEY ([''+ cast(fc.name as varchar(255)) +''])
      REFERENCES [PIMSUser].[dbo].[''+ cast(p.name as varchar(255)) +''] ([''+cast(rc.name as varchar(255))+''])''
FROM  sysobjects f
      INNER JOIN sys.sysobjects c ON f.parent_obj = c.id
      INNER JOIN sys.sysreferences r ON f.id = r.constid
      INNER JOIN sys.sysobjects p ON r.rkeyid = p.id
      INNER JOIN sys.syscolumns rc ON r.rkeyid = rc.id and r.rkey1 = rc.colid
      INNER JOIN sys.syscolumns fc ON r.fkeyid = fc.id and r.fkey1 = fc.colid
WHERE 
      f.type = ''F''
      AND
      cast(p.name as varchar(255)) IN (SELECT * FROM @myTempTable)

Luego copio las declaraciones para ejecutarlas, pero con un poco de esfuerzo de desarrollo podrías usar un cursor para ejecutarlas dinámicamente.

Answer 10

No hagas esto! Realmente, no es una buena idea.

Si sabe qué tablas desea truncar, cree un procedimiento almacenado que las trunque. Puede solucionar el problema para evitar problemas de claves externas.

Si realmente quieres truncarlos a todos (para que BCP los cargue, por ejemplo), serás tan rápido como para abandonar la base de datos y crear una nueva desde cero, lo que tendría el beneficio adicional de saber exactamente dónde estás.

Answer 11

No veo por qué borrar los datos sería mejor que un script para descartar y volver a crear cada tabla.

Eso o mantener una copia de seguridad de su base de datos vacía y restaurarla sobre una anterior

Answer 12

Para SQL 2005,

EXEC sp_MSForEachTable ''TRUNCATE TABLE ?''

Un par de enlaces más para 2000 y 2005/2008 .

Answer 13

Si desea mantener los datos en una tabla particular (es decir, una tabla de búsqueda estática) al eliminar / truncar datos en otras tablas dentro del mismo archivo base, entonces necesita un ciclo con las excepciones en él. Esto es lo que estaba buscando cuando tropecé con esta pregunta.

sp_MSForEachTable parece un error para mí (es decir, comportamiento incoherente con las declaraciones IF), que es probablemente la razón por la cual no está documentado por MS.

declare @LastObjectID int = 0
declare @TableName nvarchar(100) = ''''
set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
while(@LastObjectID is not null)
begin
    set @TableName = (select top 1 [name] from sys.tables where [object_id] = @LastObjectID)

    if(@TableName not in (''Profiles'', ''ClientDetails'', ''Addresses'', ''AgentDetails'', ''ChainCodes'', ''VendorDetails''))
    begin
        exec(''truncate table ['' + @TableName + '']'')
    end 

    set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
end

Answer 14

Truncar todas las tablas solo funcionará si no tiene ninguna relación de clave externa entre sus tablas, ya que SQL Server no le permitirá truncar una tabla con una clave externa.

Una alternativa a esto es determinar las tablas con claves externas y eliminarlas primero, luego puede truncar las tablas sin claves externas después.

Consulte http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 y http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 para obtener más detalles.

Answer 15

Una opción alternativa que me gusta usar con MSSQL Server Deveploper o Enterprise es crear una instantánea de la base de datos inmediatamente después de crear el esquema vacío. En ese momento, puede seguir restableciendo la base de datos a la instantánea.

Answer 16

selecciona ''eliminar de'' + TABLE_NAME de INFORMATION_SCHEMA.TABLES donde TABLE_TYPE = ''BASE TABLE''

donde el resultado viene.

Copie y pegue en la ventana de consulta y ejecute el comando