Como desactivar todas las claves foráneas de una tabla: Desactivar claves foráneas de una tabla
Como desactivar claves foráneas de una tabla
Un día, una persona comento que si es posible desactivar las claves foráneas de una tabla mientras se realiza una operación de DELETE, nunca he tenido esa necesidad sin embargo, el requerimiento es curioso la tabla tiene 100 relaciones o mas, y al realizar un DELETE, de un gran conjunto de datos es muy lento, por lo cual se creo un SP que pueda realizar dicho requerimiento
|
1 |
/* CREAS EL PROCEDIMIENTO ALMANCENADO QUE ESTA EN EL CODIGO Y PARA ELIMINAR USAS LOS PASOS QUE TE ENUMERO*/ |
|
1 2 |
--*********************************************************************************************************** EXECUTE OperationSQLForeignKey 'DISABLE','AQUI_TU_TABLA','AQUI_SU_ESQUEMA' |
|
1 2 |
--*********************************************************************************************************** DELETE FROM AQUI_TU_TABLA WHERE 'LO QUE QUIERAS' |
|
1 2 3 |
--*********************************************************************************************************** EXECUTE OperationSQLForeignKey 'ENABLE','AQUI_TU_TABLA','AQUI_SU_ESQUEMA' /pre> |
|
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 |
--*********************************************************************************************************** --****************Procedimiento para realizar ENABLE OR DISABLE las claves foráneas de una tabla *********************** --*********************************************************************************************************** CREATE PROCEDURE OperationSQLForeignKey ( @operation VARCHAR(10), @tableName sysname, @schemaName sysname ) AS BEGIN SET NOCOUNT ON 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 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 IF @operation = 'ENABLE' BEGIN SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] CHECK CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd EXEC sp_executesql @cmd END IF @operation = 'DISABLE' BEGIN SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] NOCHECK CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd EXEC sp_executesql @cmd END IF @operation = 'DROP' BEGIN SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] DROP CONSTRAINT [' + @FK_NAME + ']' PRINT @cmd EXEC sp_executesql @cmd DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT 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 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 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 END --*********************************************************************************************************** --**************** END PROCEDURE **************************************************************************** --*********************************************************************************************************** |
Eres el mejor!!! muchas gracias