Últimas noticias
Loading...
miércoles, 2 de abril de 2008

SQL Server: Metadatos (II) - Aplicación práctica - Vaciar base de datos

Continuando con el artículo en el que hablaba de la utilidad de los Metadatos para obtener información de nuestra base de datos, vamos a escribir un procedimiento almacenado cuya finalidad será la de vaciar todo el contenido (datos) de nuestra base de datos.

Este procedimiento resultará muy útil en aquellas bases de datos que contengan multitud de tablas relacionadas entre si. En estos casos, nos vemos obligados a ir eliminando los datos tabla a tabla, respetando las reglas de integridad, lo cual se convierte en una tarea muy complicada. Para automatizar este proceso seguiremos los siguientes pasos:

  1. Deshabilitar las restricciones de cada tabla de la base de datos.
  2. Vaciar los registros de las distintas tablas de la base de datos.
  3. Volver a habilitar las restricciones definidas en las tablas de la base de datos.
Y por supuesto, realizar una copia de seguridad de nuestra base de datos antes de ejecutar el script, por si algo fuese mal. Además como medida de seguridad adicional, podemos incluir al inicio del script, la instrucción Use, con tal de seleccionar la BBDD en la que queremos trabajar:

Use [miBaseDeDatos]

1. Deshabilitar restricciones de las tablas de la BBDD.

Para ello vamos a recorrer todas las restricciones de nuestra base de datos con la ayuda de la información contenida en information_schema.table_constraints. Además vamos a aprovechar para deshabilitar cada restricción a medida que vamos recorriendo el cursor.

DECLARE CURSOR_ALTER
CURSOR FOR
SELECT 'ALTER TABLE ' + TABLE_NAME + ' NOCHECK CONSTRAINT ' + CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.table_constraints
ORDER BY TABLE_NAME, CONSTRAINT_NAME
OPEN CURSOR_ALTER FETCH NEXT FROM CURSOR_ALTER INTO @PROXIMA_SENTENCIA, @NOM_TABLA, @NOM_CONSTRAINT
WHILE @@FETCH_STATUS = 0
BEGIN
exec (@PROXIMA_SENTENCIA)
FETCH NEXT FROM CURSOR_ALTER INTO @PROXIMA_SENTENCIA , @NOM_TABLA, @NOM_CONSTRAINT
END
/* Libera la memoria */
CLOSE CURSOR_ALTER
DEALLOCATE CURSOR_ALTER

07/05/2013 Actualización

En las nuevas versiones de SQL Server (yo lo he probado en SQL Server 2012), se puede ejecutar el siguiente script para realizar la misma tarea:


-- Deshabilitar
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 
-- Habilitar
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"


2. Vaciar los registros de las distintas tablas de la base de datos.

De igual forma que en el paso anterior, vamos a recorrer todas las tablas de nuestra base de datos y eliminar sus registros. Para ello, utilizaremos la información que nos brinda information_schema.tables. Es muy importante que limitemos de alguna forma las tablas que se van a vaciar, por ejemplo, si las tablas de nuestra aplicación empiezan por TBL_ podríamos especificar en la condición WHERE que filtre por aquellas tablas que empiezan con este nombre. En el código que tenemos a continuación, se eliminarán los registros de todas las tablas excepto de sysdiagrams.

DECLARE CURSOR_DELETE
CURSOR FOR
SELECT 'DELETE FROM ' + TABLE_NAME, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_NAME not like 'sysdiagrams'
ORDER BY TABLE_NAME
OPEN CURSOR_DELETE FETCH NEXT FROM CURSOR_DELETE INTO @SQL_DELETE, @NOM_TABLA
WHILE @@FETCH_STATUS = 0
BEGIN
exec (@SQL_DELETE)
FETCH NEXT FROM CURSOR_DELETE INTO @SQL_DELETE, @NOM_TABLA
END
/* Libera la memoria */
CLOSE CURSOR_DELETE
DEALLOCATE CURSOR_DELETE
3. Habilitar restricciones de las tablas de la BBDD.
Vamos a realizar el proceso inverso al seguido en el paso 1. De nuevo, haremos uso de la información contenida en information_schema.table_constraints. En este caso, utilizaremos la instrucción CHECK CONSTRAINT, que vuelve a activar la restricción.
DECLARE CURSOR_ALTER
CURSOR FOR
SELECT 'ALTER TABLE ' + TABLE_NAME + ' CHECK CONSTRAINT ' + CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.table_constraints
ORDER BY TABLE_NAME, CONSTRAINT_NAME
OPEN CURSOR_ALTER FETCH NEXT FROM CURSOR_ALTER INTO @PROXIMA_SENTENCIA, @NOM_TABLA, @NOM_CONSTRAINT
WHILE @@FETCH_STATUS = 0
BEGIN
exec (@PROXIMA_SENTENCIA)
FETCH NEXT FROM CURSOR_ALTER INTO @PROXIMA_SENTENCIA , @NOM_TABLA, @NOM_CONSTRAINT
END
/* Libera la memoria */
CLOSE CURSOR_ALTER
DEALLOCATE CURSOR_ALTER


Una vez ejecutados los tres pasos, habremos vaciado toda la información de nuestra base de datos. Como recomendación, se deberían limitar aquellas tablas de las que vamos a borrar los registros mediante la correspondiente instrucción dentro de la clausula WHERE (en el paso 2). También es interesante incluir comentarios y sentencias print que nos den una idea de la acción que esta realizando en cada momento el script, y de aquellas tablas que han sufrido cambios.

6 comentarios:

SKLR dijo...

hey, buenisimo! es justo lo que buscaba!

Pablo Barrachina dijo...

Me alegro, a ver si me animo a publicar nuevos artículos, el trabajo del día a día lo hace muy complicado.
Gracias!

Luis Enrique AM dijo...

Hola amigo. Tengo un problemilla, me sale este error.

Debe declarar la variable escalar "@PROXIMA_SENTENCIA".

También me sale con @SQL_DELETE.

¿Sabes por que?. Saludos amigo.

Pablo Barrachina dijo...

Hola Luis Enrique,

Estos scripts son de una versión antigua de SQL Server, creo que era la 2000. Imagino que entonces no era obligatorio declarar las variables que utilizas, pero en la versión que estás trabajando parece que es obligatorio.

Declara las variables con la sentencia:

DECLARE @PROXIMA_SENTENCIA VARCHAR(1000)

o

DECLARE @SQL_DELETE VARCHAR(1000)

El tamaño de la variable puedes calcularlo tu mismo para que se ajuste un poco más a la realidad.

Tendrás que declarar todas las variables que se utilizan en el script.

Pablo Barrachina dijo...

Luis Enrique, he incorporado un script para realizar la activación o desactivación de las foreign keys en versiones más recientes de SQL Server.

Saludos!

Luis Enrique AM dijo...

Muchas gracias Pablo, me sirvió mucho la vdd. Saludos desde México.

Quick Message
Press Esc to close
Copyright © 2013 Apaga y vámonos All Right Reserved