Eliminar o limpiar tablas relacionadas entre si
Publicado por Fabio (1 intervención) el 11/03/2015 19:48:09
Tengo una base de datos con muchas relaciones (dependencias) entre tablas por claves foráneas (FK) y al momento de limpiar las tablas, nos damos con el problema de que debemos limpiar previamente las tablas relacionadas para liberar los IDs que usamos en las otras.
Una de las formas de hacer esto es antes de eliminar o limpiar una tabla, buscar las dependencias (Click derecho sobre el nombre de la tabla en el explorador de objetos de la base, opción Ver dependencias), ver todas las relaciones y comenzar borrando desde las ultimas hasta llegar a la tabla que nos interesa.
Para unas pocas tablas, esto puede servir, pero cuando tenes muchas, Se complica!
Por eso me puse a buscar como obtener esta relaciones desde el TransacSQL y crear un pequeño código que haga las cosas por nosotros. Se los comparto para el que lo necesite. Cualquier duda, me avisan o si lo mejoran, espero que lo sigan compartiendo para el bien de la comunidad.
-----------------------------------------------------------------------------------------------------------------------
DECLARE @PASO INT
-- Obtener las tablas que necesitamos borrar (se puede agregar un where y filtrar lo necesario)
SELECT DISTINCT 9999 AS ORDEN, NAME AS TABLA, OBJECT_ID INTO #TABLAS FROM SYS.OBJECTS WHERE TYPE='U' AND NAME LIKE 'REC_%'
-- Ponerle el maximo numero de orden posible (cantidad de tablas)
SET @PASO=@@ROWCOUNT
UPDATE #TABLAS SET ORDEN=@PASO
-- Repetir en orden decreciente
WHILE @PASO>0
BEGIN
-- Reinsertar las tablas relacionadas a las tablas del paso anterior.
-- Primera vez, de todas las tablas. Resto de la veces, de las ultimas relacionadas insertadas.
-- Si una tabla se relaciona a si misma, no se incluye para evitar un loop infinito.
SET @PASO=@PASO-1
INSERT #TABLAS (ORDEN, TABLA, OBJECT_ID)
SELECT DISTINCT @PASO ORDEN, SOF.NAME TABLA, SOF.OBJECT_ID
FROM #TABLAS T
LEFT JOIN SYSREFERENCES SRE ON T.OBJECT_ID=SRE.RKEYID
LEFT JOIN SYS.OBJECTS SOF ON SOF.OBJECT_ID=SRE.FKEYID
WHERE T.ORDEN=@PASO+1 AND SOF.TYPE='U' AND SRE.RKEYID<>SRE.FKEYID
-- Si ya no hay mas relaciones, se puede terminar, sino continua con el ciclo
IF @@ROWCOUNT=0 BREAK
END
-- Mostrar las tablas ordenadas desde las ultimas insertadas (que no tienen relaciones con otras pero hay otras que se relacionan con ellas)
-- hasta las primeras insertadas (que tienen relaciones con las demas)
SELECT 'TRUNCATE TABLE' INSTRUCCION, TABLA FROM #TABLAS GROUP BY TABLA ORDER BY MIN(ORDEN)
-- Poner la instruccion de TSQL que se necesite: DROP TABLE, TRUNCATE TABLE, DELETE, etc.
-- Copiar el resultado de este Script y ejecutarlo en una ventana de consulta del MSSQL Management Studio
DROP TABLE #TABLAS
-------------------------------------------------------------------------------------------------------------------------
Algunas explicaciones:
- Usé las tablas SYS.OBJECTS y SYSREFERENCES (esta última, tengo entendido que es de SQL2008 y se mantiene por compatibilidad en el SQL2012) si alguien conoce otra que reemplace a la SysReferences en nuevas versiones, que avise.
- La idea es obtener en la primera consulta todas las tablas que necesitemos "Limpiar". Cada uno se las arreglará para poner el WHERE necesario.
- Esa lista servirá como ultimo paso de la limpieza. Allí comienza un ciclo para ir poniendo antes (paso-1) las tablas que están relacionadas a estas primeras. En el próximo paso del ciclo se pondrán antes (paso-1), las relaciones de las relaciones anteriores, hasta no tener mas relaciones.
- Al final se obtienen las tablas (una sola vez cada una) ordenadas desde las ultimas insertadas hasta las primeras (orden que necesitamos según las relaciones que poseen cada una)
- El resultado de la ejecución de este script, es una lista de comandos para limpiar, que podes copiar y pegar en una consulta para limpiar de manera ordenada tus tablas relacionadas.
- No costaría mucho agregarle la misma ejecución de la limpieza en base al resultado obtenido, pero eso se lo dejo para el que lo necesite. si necesitan ayuda sobre esto, me lo piden y veo como puedo ayudarlos.
Una de las formas de hacer esto es antes de eliminar o limpiar una tabla, buscar las dependencias (Click derecho sobre el nombre de la tabla en el explorador de objetos de la base, opción Ver dependencias), ver todas las relaciones y comenzar borrando desde las ultimas hasta llegar a la tabla que nos interesa.
Para unas pocas tablas, esto puede servir, pero cuando tenes muchas, Se complica!
Por eso me puse a buscar como obtener esta relaciones desde el TransacSQL y crear un pequeño código que haga las cosas por nosotros. Se los comparto para el que lo necesite. Cualquier duda, me avisan o si lo mejoran, espero que lo sigan compartiendo para el bien de la comunidad.
-----------------------------------------------------------------------------------------------------------------------
DECLARE @PASO INT
-- Obtener las tablas que necesitamos borrar (se puede agregar un where y filtrar lo necesario)
SELECT DISTINCT 9999 AS ORDEN, NAME AS TABLA, OBJECT_ID INTO #TABLAS FROM SYS.OBJECTS WHERE TYPE='U' AND NAME LIKE 'REC_%'
-- Ponerle el maximo numero de orden posible (cantidad de tablas)
SET @PASO=@@ROWCOUNT
UPDATE #TABLAS SET ORDEN=@PASO
-- Repetir en orden decreciente
WHILE @PASO>0
BEGIN
-- Reinsertar las tablas relacionadas a las tablas del paso anterior.
-- Primera vez, de todas las tablas. Resto de la veces, de las ultimas relacionadas insertadas.
-- Si una tabla se relaciona a si misma, no se incluye para evitar un loop infinito.
SET @PASO=@PASO-1
INSERT #TABLAS (ORDEN, TABLA, OBJECT_ID)
SELECT DISTINCT @PASO ORDEN, SOF.NAME TABLA, SOF.OBJECT_ID
FROM #TABLAS T
LEFT JOIN SYSREFERENCES SRE ON T.OBJECT_ID=SRE.RKEYID
LEFT JOIN SYS.OBJECTS SOF ON SOF.OBJECT_ID=SRE.FKEYID
WHERE T.ORDEN=@PASO+1 AND SOF.TYPE='U' AND SRE.RKEYID<>SRE.FKEYID
-- Si ya no hay mas relaciones, se puede terminar, sino continua con el ciclo
IF @@ROWCOUNT=0 BREAK
END
-- Mostrar las tablas ordenadas desde las ultimas insertadas (que no tienen relaciones con otras pero hay otras que se relacionan con ellas)
-- hasta las primeras insertadas (que tienen relaciones con las demas)
SELECT 'TRUNCATE TABLE' INSTRUCCION, TABLA FROM #TABLAS GROUP BY TABLA ORDER BY MIN(ORDEN)
-- Poner la instruccion de TSQL que se necesite: DROP TABLE, TRUNCATE TABLE, DELETE, etc.
-- Copiar el resultado de este Script y ejecutarlo en una ventana de consulta del MSSQL Management Studio
DROP TABLE #TABLAS
-------------------------------------------------------------------------------------------------------------------------
Algunas explicaciones:
- Usé las tablas SYS.OBJECTS y SYSREFERENCES (esta última, tengo entendido que es de SQL2008 y se mantiene por compatibilidad en el SQL2012) si alguien conoce otra que reemplace a la SysReferences en nuevas versiones, que avise.
- La idea es obtener en la primera consulta todas las tablas que necesitemos "Limpiar". Cada uno se las arreglará para poner el WHERE necesario.
- Esa lista servirá como ultimo paso de la limpieza. Allí comienza un ciclo para ir poniendo antes (paso-1) las tablas que están relacionadas a estas primeras. En el próximo paso del ciclo se pondrán antes (paso-1), las relaciones de las relaciones anteriores, hasta no tener mas relaciones.
- Al final se obtienen las tablas (una sola vez cada una) ordenadas desde las ultimas insertadas hasta las primeras (orden que necesitamos según las relaciones que poseen cada una)
- El resultado de la ejecución de este script, es una lista de comandos para limpiar, que podes copiar y pegar en una consulta para limpiar de manera ordenada tus tablas relacionadas.
- No costaría mucho agregarle la misma ejecución de la limpieza en base al resultado obtenido, pero eso se lo dejo para el que lo necesite. si necesitan ayuda sobre esto, me lo piden y veo como puedo ayudarlos.
Valora esta pregunta
0
