SQL Server - Eliminar o limpiar tablas relacionadas entre si

   
Vista:

Eliminar o limpiar tablas relacionadas entre si

Publicado por Fabio zerog.fa@hotmail.com (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.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Isaias

Eliminar o limpiar tablas relacionadas entre si

Publicado por Isaias (3182 intervenciones) el 11/03/2015 22:28:35
Amigo, ¿no seria mejor APAGAR los constrains y una vez hecha tu limpieza de datos, volverlos a PRENDER?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
SET NOCOUNT ON
 
DECLARE @operation VARCHAR(10)
DECLARE @tableName sysname
DECLARE @schemaName sysname
 
SET @operation = 'ENABLE' --ENABLE, DISABLE, DROP
 
-- Para hacer para todas las tablas, voy a crear una lista de todas las tablas.
CREATE TABLE #Tables (idTable int identity(1,1), TableName sysname, schemaName sysname)
INSERT INTO #Tables (TableName, schemaName)
SELECT name, SCHEMA_NAME(schema_id) AS schema_name
FROM SYS.OBJECTS WHERE type_desc = 'USER_TABLE'
 
--SET @tableName = 'SpecialOfferProduct'
--SET @schemaName = 'dbo'
 
DECLARE @cmd NVARCHAR(1000)
 
DECLARE
   @FK_NAME sysname,
   @FK_OBJECTID INT,
   @FK_DISABLED INT,
   @FK_NOT_FOR_REPLICATION INT,
   @DELETE_RULE    smallint,
   @UPDATE_RULE    smallint,
   @FKTABLE_NAME sysname,
   @FKTABLE_OWNER sysname,
   @PKTABLE_NAME sysname,
   @PKTABLE_OWNER sysname,
   @FKCOLUMN_NAME sysname,
   @PKCOLUMN_NAME sysname,
   @CONSTRAINT_COLID INT,
   @iCont INT = 1
 
WHILE @iCont < (SELECT MAX(idTable) FROM #Tables)
 BEGIN
    SET @tableName = (SELECT TableName FROM #Tables WHERE idTable = @iCont)
	SET @schemaName = (SELECT schemaName FROM #Tables WHERE idTable = @iCont)
	DECLARE cursor_fkeys CURSOR FOR
	   SELECT  Fk.name,
			   Fk.OBJECT_ID,
			   Fk.is_disabled,
			   Fk.is_not_for_replication,
			   Fk.delete_referential_action,
			   Fk.update_referential_action,
			   OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,
			   schema_name(Fk.schema_id) AS Fk_table_schema,
			   TbR.name AS Pk_table_name,
			   schema_name(TbR.schema_id) Pk_table_schema
	   FROM    sys.foreign_keys Fk LEFT OUTER JOIN
			   sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join
	   WHERE   TbR.name = @tableName
			   AND schema_name(TbR.schema_id) = @schemaName
 
	OPEN cursor_fkeys
 
	FETCH NEXT FROM   cursor_fkeys
	   INTO @FK_NAME,@FK_OBJECTID,
		   @FK_DISABLED,
		   @FK_NOT_FOR_REPLICATION,
		   @DELETE_RULE,
		   @UPDATE_RULE,
		   @FKTABLE_NAME,
		   @FKTABLE_OWNER,
		   @PKTABLE_NAME,
		   @PKTABLE_OWNER
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
 
	   -- create statement for enabling FK
	   IF @operation = 'ENABLE'
	   BEGIN
		   SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
			   + ']  CHECK CONSTRAINT [' + @FK_NAME + ']'
 
		  PRINT @cmd
	   END
 
	   -- create statement for disabling FK
	   IF @operation = 'DISABLE'
	   BEGIN
		   SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
			   + ']  NOCHECK CONSTRAINT [' + @FK_NAME + ']'
 
		  PRINT @cmd
	   END
 
	   -- create statement for dropping FK and also for recreating FK
	   IF @operation = 'DROP'
	   BEGIN
 
		   -- drop statement
		   SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
		   + ']  DROP CONSTRAINT [' + @FK_NAME + ']'
 
		  PRINT @cmd
 
		   -- create process
		   DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT
 
		   -- create cursor to get FK columns
		   DECLARE cursor_fkeyCols CURSOR FOR
		   SELECT  COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,
				   COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
		   FROM    sys.foreign_keys Fk LEFT OUTER JOIN
				   sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN
				   sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
		   WHERE   TbR.name = @tableName
				   AND schema_name(TbR.schema_id) = @schemaName
				   AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008
		   ORDER BY Fk_Cl.constraint_column_id
 
		   OPEN cursor_fkeyCols
 
		   FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
 
		   SET @COUNTER = 1
		   SET @FKCOLUMNS = ''
		   SET @PKCOLUMNS = ''
 
		   WHILE @@FETCH_STATUS = 0
		   BEGIN
 
			   IF @COUNTER > 1
			   BEGIN
				   SET @FKCOLUMNS = @FKCOLUMNS + ','
				   SET @PKCOLUMNS = @PKCOLUMNS + ','
			   END
 
			   SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
			   SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'
 
			   SET @COUNTER = @COUNTER + 1
 
			   FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
		   END
 
		   CLOSE cursor_fkeyCols
		   DEALLOCATE cursor_fkeyCols
 
		   -- generate create FK statement
		   SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +
			   CASE @FK_DISABLED
				   WHEN 0 THEN ' CHECK '
				   WHEN 1 THEN ' NOCHECK '
			   END +  ' ADD CONSTRAINT [' + @FK_NAME
			   + '] FOREIGN KEY (' + @FKCOLUMNS
			   + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('
			   + @PKCOLUMNS + ') ON UPDATE ' +
			   CASE @UPDATE_RULE
				   WHEN 0 THEN ' NO ACTION '
				   WHEN 1 THEN ' CASCADE '
				   WHEN 2 THEN ' SET_NULL '
				   END + ' ON DELETE ' +
			   CASE @DELETE_RULE
				   WHEN 0 THEN ' NO ACTION '
				   WHEN 1 THEN ' CASCADE '
				   WHEN 2 THEN ' SET_NULL '
				   END + '' +
			   CASE @FK_NOT_FOR_REPLICATION
				   WHEN 0 THEN ''
				   WHEN 1 THEN ' NOT FOR REPLICATION '
			   END
 
		  PRINT @cmd
 
	   END
 
	   FETCH NEXT FROM    cursor_fkeys
		  INTO @FK_NAME,@FK_OBJECTID,
			   @FK_DISABLED,
			   @FK_NOT_FOR_REPLICATION,
			   @DELETE_RULE,
			   @UPDATE_RULE,
			   @FKTABLE_NAME,
			   @FKTABLE_OWNER,
			   @PKTABLE_NAME,
			   @PKTABLE_OWNER
	END
 
	CLOSE cursor_fkeys
	DEALLOCATE cursor_fkeys
 
	SET @iCont = @iCont + 1
END
 
DROP TABLE #Tables
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Eliminar o limpiar tablas relacionadas entre si

Publicado por jams (75 intervenciones) el 13/03/2015 21:45:33
Dependiendo del comando que uses para el borrado, podras o no simplemente deshabilitar los constraints, en caso de que no te permita borrar los datos simplemente con el deshabilitado(por ejemplo si usas truncate table) deberas entonces borrar los constraints y truncar tus tablas y volver a generar tus constraints, este proceso es mucho mas simple que el que tu planteas, para hacer este proceso de borrar y crear apoyate del asistente Generate scripts y listo.

el comentario de Isais es en parte cierto pero imcompleto



Saludos y suerte
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

Eliminar o limpiar tablas relacionadas entre si

Publicado por Isaias (3182 intervenciones) el 17/03/2015 23:39:02
Me gustaría saber, porque esta "incompleto" Gracias
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Eliminar o limpiar tablas relacionadas entre si

Publicado por jams (75 intervenciones) el 18/03/2015 18:39:41
Esta incompleto por lo que ya comente, dependiendo del comando que este usando para el borrado de la informacion, si usa truncate no podra simplemente deshabilitar en este caso debera borrar los constraints borrar los datos y crear nuevamente los constraints, si usa delete si podra solamente deshabilitar ( "apagar" como lo mencionas) borrar los datos y habiltar constraints un borrado por cierto mas lento.



Saludos y suerte
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar