René Pacios

/* Overflow My Brain & More */

Vaciar todas las tablas de una Base de Datos SQL Server

Esta va a ser la primera, espero que de muchas, entrada donde comentaré un pequeño truquillo para eliminar todos los registros de todas las tablas de una base de datos de SQL Server.

Es muy corriente que mientras estamos desarrollando o haciendo pruebas hagamos inserciones, modificaciones y demás, y dejemos la base de datos llena de registros sin sentido. No voy a entrar si usamos Objetos tipo Mock o no,vamos a suponer que estamos desarrollando nuestro "programa de videoclub" para clase y  hacemos pruebas a medida que desarrollamos.

Para eliminar todos los registros de las tablas podríamos utilizar un cursor sobre  las tablas obtenidas de  sys.objects, y ejecutar SQL dinámico vaciando así las tablas y teniendo cuidad del orden para cumplir con las restricciones, etc.

Sin embargo SQL Server posee un par de procedimientos almacenados, no documentados en los libros online de SQL Server , bastante curiosos que nos van a servir para simplificar nuestro trabajo. Estos son sp_MSforeachtable y sb_MSforeachdb que tal y como se infiere por el nombre de los mismos nos va a permitir aplicar un comando o un conjunto de ellos a todas las tablas de una base de datos o a todas las bases de datos de una instancia de SQL Server.

Para nuestro el caso que nos concierne, vamos a utilizar el primer procedure, primero veamos el script

USE DPFC_PRUEBAS -- OJO CAMBIAR EL NOMBRE DE LA BD
GO

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
GO

EXEC sp_MSForEachTable
    'BEGIN TRY
        TRUNCATE TABLE ?
    END TRY
    BEGIN CATCH
        DELETE FROM ?
    END CATCH;' 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
GO

 

Como se puede apreciar en el ejemplo, lo primero que se debe hacer es deshabilitar todos los triggers y las restricciones de cada tabla para evitar efectos colaterales provocados por el borrado en cada tabla. 

A continuación se eliminan los datos de cada una de las tablas.

Acto seguido, volvemos a habilitar las restricciones de tipo check y los triggers para que la Base de Datos sigua funcionando con normalidad.

Espero que os resulte de utilidad, y por otro lado también aprovecho que donde mejor que en el blog para dejar mis chuletas. Sonrisa

Nos leemos, René Pacios

Acerca de René

René Pacios es un apasionado de la tecnología, autodidacta, emprendedor, le encanta el desarrollo web, para moviles, aplicaciones, todo aquello que automatice tareas y haga que las máquinas trabajen para él. Es un gran fan de las tecnologías Microsoft, y le encanta estar a la última siempre que el tiempo se lo permite. Siempre quiso ser cantante, pero creo que en esta vida se va a quedar sólo en canta-mañanas

               

Comentarios (27) -

  • Juan

    14/10/2013 23:12:34 | Responder

    Gracias René, muy buen block, muy util gracias, solamente una consulta, si tengo muchas tablas, por ejemplo unas 200 tablas, debo de hacer una por una como en el script?, o hay alguna forma de aplicar todas las tablas sin especificar los nombres.
    Gracias

  • René Pacios

    14/10/2013 23:45:53 | Responder

    Muchas gracias @Juan, me alegro de que te parezca útil. No entiendo muy bien tu duda, el script funciona con cualquier número de tablas,siempre que estén en la misma base de datos, podrías aplicar el script tal y como está a tu base de datos de 200 tablas y las limpiaría sin problema.

    Salu2

  • Javier Pardo

    05/05/2014 15:47:50 | Responder

    Muchas gracias por el aporte. Me ha sido de mucha utilidad.

  • Juan

    26/05/2014 19:07:37 | Responder

    Como podría pasar todo el Script anterior, a un procedimiento almacenado?

    • Magdalena

      03/07/2014 18:49:55 | Responder

      Juan...

      Ese trozo de script lo que hace es justamente borrar todos los registros de todas las tablas de la base de datos que tu le indiques, sin necesidad de ir tabla por tabla.

  • Magdalena

    03/07/2014 18:47:42 | Responder

    Estimado...

    Era justo lo que necesitaba... muchisimas gracias por el aporte...

  • Augusto

    14/11/2014 21:31:38 | Responder

    Pero como hago que las laves primarias siga contando desde 0

  • Augusto

    14/11/2014 21:44:47 | Responder

    Bien rene gracias por el aporte pero tengo una duda como yo también quiero que se inicien las claves primaras desde 1 por ejemplo

    • René Pacios

      14/11/2014 22:51:43 | Responder

      Buenas Augusto, entiendo que cuando te refieres a reiniciar las claves estás hablando de las columnas de tipo Identity.

      Al vaciar las tablas, al hacer inserts, estas columnas volverán a comenzar a incrementarse desde el inicio.

      Espero haberte solucionado la duda.

  • Omar

    10/12/2014 22:01:53 | Responder

    Muchas gracias, ahorra bastante tiempo.

  • Natzú

    18/03/2015 18:01:31 | Responder

    Se puede usar este sp exceptuando algunas tablas??


    Excelente Aporte Gracias!!!!

    • Ervinus

      26/03/2015 18:33:44 | Responder

      Eso era lo que yo quería... ahí va el codigo que yo usé... (no quería eliminar las tablas que tuvieran el prifico SEC_ o el prefijo GEN_)

      DECLARE db_cursor CURSOR FOR  
      select  TABLE_NAME  from INFORMATION_SCHEMA.TABLES
      where TABLE_NAME NOT like 'SEC_%' AND TABLE_NAME NOT like 'GEN_%' and TABLE_TYPE='BASE TABLE'

      OPEN db_cursor  
      FETCH NEXT FROM db_cursor INTO @name  

      WHILE @@FETCH_STATUS = 0  
      BEGIN  
          set @text=''
          set @text='ALTER TABLE ' + @name + ' NOCHECK CONSTRAINT ALL;'
          
          set @text=@text + 'ALTER TABLE ' + @name + ' DISABLE TRIGGER ALL;'
          
          
          set @text=@text + 'BEGIN TRY
              TRUNCATE TABLE  ' + @name + '
            END TRY
            BEGIN CATCH
              DELETE FROM  ' + @name + '
            END CATCH;'
          set @text=@text +  'ALTER TABLE  ' + @name + '  CHECK CONSTRAINT ALL;'
          
          set @text=@text +  'ALTER TABLE  ' + @name + '  ENABLE TRIGGER ALL;'
          
          EXEC sp_executesql @text
             FETCH NEXT FROM db_cursor INTO @name  
      END  

      CLOSE db_cursor  
      DEALLOCATE db_cursor

  • Ervinus

    26/03/2015 18:31:34 | Responder

    Gracias bro! llevaba media hora tratando de vaciar más de 700 tablas. Modifiqué un poquito tu código, lo pongo por si le sirve a alguien... el ejemplo del cursor lo saqué de www.mssqltips.com/.../

    DECLARE @name NVARCHAR(250)
    declare @text nvarchar(max)


    DECLARE db_cursor CURSOR FOR  
    select  TABLE_NAME  from INFORMATION_SCHEMA.TABLES
    where TABLE_NAME NOT like 'SEC_%' AND TABLE_NAME NOT like 'GEN_%' and TABLE_TYPE='BASE TABLE'

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        set @text=''
        set @text='ALTER TABLE ' + @name + ' NOCHECK CONSTRAINT ALL;'
        
        set @text=@text + 'ALTER TABLE ' + @name + ' DISABLE TRIGGER ALL;'
        
        
        set @text=@text + 'BEGIN TRY
            TRUNCATE TABLE  ' + @name + '
          END TRY
          BEGIN CATCH
            DELETE FROM  ' + @name + '
          END CATCH;'
        set @text=@text +  'ALTER TABLE  ' + @name + '  CHECK CONSTRAINT ALL;'
        
        set @text=@text +  'ALTER TABLE  ' + @name + '  ENABLE TRIGGER ALL;'
        
        EXEC sp_executesql @text
           FETCH NEXT FROM db_cursor INTO @name  
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

  • René Pacios

    26/03/2015 20:19:56 | Responder

    @Ervinus muchas gracias por el aporte.

  • Pablo Vasquez

    19/06/2015 17:13:01 | Responder

    Muy buen Aporte Rene me gustaria añadirte a mi facebook como te puedo buscar para consultarte cuando tenga problemas

  • Jafet Granados

    03/07/2015 22:56:00 | Responder

    Gracias por el aporte amigo. Existe otra posible solución, un poco más rápida para realizar dicha tarea y no usa codito de script, sin embargo, sí te genera un script. Dicha solución es realizando una copia en sqlserver. Click derecho en la BD>> Tareas>> Generar Script. En el cuadro de dialogo Generar Script dar en Siguiente >> Seleccionar "Incluir en el script toda la BD y todos los objetos de la BD" >> Siguiente>> Click en Boton Avanzadas >> en la opcion "Tipo de datos que desea incluir en el script" seleccionar "Solo esquema" (hay otras opciones que les pueden llamar la atencion) >> Aceptar >> Siguiente y >> Siguiente. Con esto SqlServer les genera un script con el esquema de la BD, lo ejecutan y listo. Tienen la misma BD vacia, y sin tanto rollo. Ojo, les recomiendo abrir el script con el block de notas antes de ejecutarlo y cambiar el nombre de la BD, por ejemplo a BDPruebas, ya que de lo contrario les va a dar error. Para hacer esto mas facil pueden dar Ctrl - R (Reemplazar) y en el primer campo dan el nombre de la actual BD y en el segundo campo el nombre que le quieran dar a la nueva BD. Con esto es mas que suficiente. Espero que les sirva mi aporte. Saludos

  • René Pacios

    04/07/2015 11:05:42 | Responder

    Muchas gracias por el comentario @Jafet

    La solución que propones es tener el script de creación de la BD, que es perfectamente válido. Incluso en vez de renombrar el nombre de la BD como indicas, al generar el script, en el último paso del asistente creo recordar, utilizando el botón de opciones avanzadas, se puede indicar que elimine los objetos si existen antes de crearlos, de ese modo machacaría tu BD y no sería necesario el renombrado.

    Sin embargo, yo prefiero utilizar la opción del script, ya que si estoy desarrollo (que es cuando más suelo utilizar este script) y cambio esquema de tabla por ejemplo, no necesito generar un nuevo script, y tampoco afecta a los objetos dependientes como procedures, triggers, etc..

    De todos modos, sólo es una opinión evidentemente, me pareció un script muy sencillo a la vez que util Smile

    Nuevamente gracias por el comentario.

    Salu2

  • Rostan

    13/07/2015 15:52:06 | Responder

    Excelente aporte Rene !!!... me saco de un apuro en menos de 2 min (: !

    • René Pacios

      13/07/2015 21:17:38 | Responder

      Muchas gracias por el comentario @Rostan me alegro de haberte ayudado.

  • jose

    28/01/2016 22:43:16 | Responder

    Una duda, al vaciar las tablas de la bd indicada, también reinicia las llaves primarias de las tablas ?

    • René Pacios Díaz

      29/01/2016 15:51:04 | Responder

      Hola @Jose,

      Gracias por el comentario.

      Supongo que te refieres a columnas de tipo identidad, vamos los auto-incrementales que configuramos en la tabla para que nos vaya generando las tablas, verdad.

      En este caso la respuesta es sí. Si utilizas, por ejemplo un campo identity que empiece en 1 ([int] IDENTITY(1,1) NOT NULL)  tras vaciar las tablas el siguiente registro que insertes se le asignará el 1 en ese campo.

      Espero haberte ayudado.

      Un saludo, René Pacios

  • johan

    01/02/2016 21:11:24 | Responder

    lo mejor

    • René

      23/05/2016 14:49:31 | Responder

      Muchas gracias Johan

  • Orlando Perez

    19/05/2016 1:05:57 | Responder

    Justamente lo que necesitaba... Gracias...

    • René

      23/05/2016 14:50:24 | Responder

      Me alegro de haberte ayudado Orlando.
      Muchas gracias por tu comentario

  • Oscar

    13/07/2016 1:22:48 | Responder

    René, muchas gracias por tu aporte, me fue de mucha ayuda y no sabía que se podía hacer de esa forma cómo está planteado en tu script. Gracias por tomarte el tiempo para compartir tu trabajo haciéndole la vida más facil a otras personas.

    • René Pacios

      13/07/2016 16:19:21 | Responder

      Me alegro de haberte ayudado Oscar.

      Muchas gracias a tí por comentar, otros me facilitan la vida a mí, y entre todos poniendo nuestro granito de arena ... Smile

Agregar comentario

Loading