Con este script se pueden habilitar o deshabilitar todos los triggers de la Base de Datos que se encuentre seleccionada.
DECLARE @cTgName VARCHAR(50)
DECLARE @cTableName VARCHAR(50)
SET NOCOUNT ON;
DECLARE Cur_TG CURSOR LOCAL FORWARD_ONLY FOR
SELECT (SCH.NAME + '.' + STG.NAME) AS TG_NAME, (SCH.NAME + '.' + SAO.NAME) AS TABLE_NAME
FROM SYS.TRIGGERS STG,
SYS.ALL_OBJECTS AS SAO,
SYS.SCHEMAS AS SCH
WHERE STG.PARENT_ID = SAO.OBJECT_ID
AND SAO.SCHEMA_ID = SCH.SCHEMA_ID
OPEN Cur_TG
FETCH Cur_TG INTO @cTgName, @cTableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRY
-- Para Habilitar
EXEC ('ENABLE TRIGGER ' + @cTgName + ' ON ' + @cTableName)
-- Para Deshabilitar
EXEC ('DISABLE TRIGGER ' + @cTgName + ' ON ' + @cTableName)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
FETCH Cur_TG INTO @cTgName, @cTableName
END -- Fin del bucle WHILE
CLOSE Cur_TG
DEALLOCATE Cur_TG
miércoles, 16 de mayo de 2012
Habilitar o Deshabilitar Llaves Foráneas en SQL Server
Con este script se habilitan o deshabilitan todos los foreing keys de la Base de Datos que se tenga seleccionada.
DECLARE @cFKName VARCHAR(50)
DECLARE @cTableName VARCHAR(50)
SET NOCOUNT ON;
DECLARE Cur_FK CURSOR LOCAL FORWARD_ONLY FOR
SELECT SFK.NAME AS FK_NAME, (SCH.NAME + '.' + SAO1.NAME) AS TABLE_NAME
FROM SYS.FOREIGN_KEYS AS SFK,
SYS.SCHEMAS AS SCH,
SYS.ALL_OBJECTS AS SAO1
WHERE SFK.SCHEMA_ID = SCH.SCHEMA_ID
AND SFK.PARENT_OBJECT_ID = SAO1.OBJECT_ID
OPEN Cur_FK
FETCH Cur_FK INTO @cFKName, @cTableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRY
-- Para Habilitar
EXEC ('ALTER TABLE ' + @cTableName + ' CHECK CONSTRAINT ' + @cFKName)
-- Para Deshabilitar
EXEC ('ALTER TABLE ' + @cTableName + ' NOCHECK CONSTRAINT ' + @cFKName)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
FETCH Cur_FK INTO @cFKName, @cTableName
END -- Fin del bucle WHILE
CLOSE Cur_FK
DEALLOCATE Cur_FK
DECLARE @cFKName VARCHAR(50)
DECLARE @cTableName VARCHAR(50)
SET NOCOUNT ON;
DECLARE Cur_FK CURSOR LOCAL FORWARD_ONLY FOR
SELECT SFK.NAME AS FK_NAME, (SCH.NAME + '.' + SAO1.NAME) AS TABLE_NAME
FROM SYS.FOREIGN_KEYS AS SFK,
SYS.SCHEMAS AS SCH,
SYS.ALL_OBJECTS AS SAO1
WHERE SFK.SCHEMA_ID = SCH.SCHEMA_ID
AND SFK.PARENT_OBJECT_ID = SAO1.OBJECT_ID
OPEN Cur_FK
FETCH Cur_FK INTO @cFKName, @cTableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRY
-- Para Habilitar
EXEC ('ALTER TABLE ' + @cTableName + ' CHECK CONSTRAINT ' + @cFKName)
-- Para Deshabilitar
EXEC ('ALTER TABLE ' + @cTableName + ' NOCHECK CONSTRAINT ' + @cFKName)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
FETCH Cur_FK INTO @cFKName, @cTableName
END -- Fin del bucle WHILE
CLOSE Cur_FK
DEALLOCATE Cur_FK
Suscribirse a:
Entradas (Atom)