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:
- Deshabilitar las restricciones de cada tabla de la base de datos.
- Vaciar los registros de las distintas tablas de la base de datos.
- Volver a habilitar las restricciones definidas en las tablas de la base de datos.
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:
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.
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.