sqlmanagementstudio_x64_esn - Cómo generar scripts para todos los desencadenantes en la base de datos utilizando Microsoft SQL Server Management Studio
sql server management studio express (6)
¿Qué tal esto?
select text from syscomments where text like ''%CREATE TRIGGER%''
EDITAR : según el comentario de jj a continuación, syscomments está en desuso y se eliminará en el futuro. Utilice las soluciones basadas en asistente o basadas en scripts enumeradas anteriormente avanzando :)
Me gustaría generar un script SQL que contenga el SQL para crear todos los disparadores que existen en nuestra base de datos. Los factores desencadenantes se agregaron directamente a través del panel de consulta de SSMS, por lo que actualmente no existe otra fuente más que el activador en la base de datos.
Ya he probado el método en el que hace clic con el botón derecho en la base de datos, selecciona Tasks->Generate Scripts y utiliza la opción " Tasks->Generate Scripts la base de datos y todos los objetos". Si bien esto crea un script SQL para las tablas y restricciones, no genera SQL para los desencadenadores.
También entiendo que puedo hacer clic con el botón derecho en cada activador de la base de datos y seleccionar la opción Generar script SQL, pero actualmente hay 46 tablas bajo auditoría (para Insertar, Actualizar y Eliminar).
En lugar de generar manualmente una secuencia de comandos de inserción, actualización y eliminación de activación para cada una de las 46 tablas, ¿hay alguna manera más fácil de hacerlo? O, ¿debería comenzar a hacer clic, copiar y pegar?
En realidad, si observas la tabla sys.comments, tiene un campo colid que es una columna numérica; si es más largo que el máximo, tendrá números> 1. Si tuviera que seleccionar uno con> 1 colID y copia ambos (si hubiera 2 registros), ¡le dará el gatillo completo!
Para ejecutar todos los desencadenadores, puede definir el procedimiento almacenado:
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
-- Procedure:
-- [dbo].[Createscriptofalltriggers]
--
-- Parameter:
-- @InclDrop bit
-- possible values:
-- 0 - Script to drop the triggers is not generated.
-- 1 - Script to drip the triggers is generated.
ALTER PROCEDURE [dbo].[Createscriptofalltriggers]
@InclDrop BIT =1
AS
DECLARE @SQL VARCHAR(8000),
@Text NVARCHAR(4000),
@BlankSpaceAdded INT,
@BasePos INT,
@CurrentPos INT,
@TextLength INT,
@LineId INT,
@MaxID INT,
@AddOnLen INT,
@LFCR INT,
@DefinedLength INT,
@SyscomText NVARCHAR(4000),
@Line NVARCHAR(1000),
@UserName SYSNAME,
@ObjID INT,
@OldTrigID INT
SET nocount ON
SET @DefinedLength = 1000
SET @BlankSpaceAdded = 0
IF @InclDrop <> 0
SET @InclDrop =1
-- This Part Validated the Input parameters
DECLARE @Triggers TABLE
(
username SYSNAME NOT NULL,
trigname SYSNAME NOT NULL,
objid INT NOT NULL
)
DECLARE @TrigText TABLE
(
objid INT NOT NULL,
lineid INT NOT NULL,
linetext NVARCHAR(1000) NULL
)
INSERT INTO @Triggers
(username,
trigname,
objid)
SELECT DISTINCT A.NAME,
B.NAME,
B.id
FROM dbo.sysusers A,
dbo.sysobjects B,
dbo.syscomments C
WHERE A.uid = B.uid
AND B.type = ''Tr''
AND B.id = C.id
AND C.encrypted = 0
IF EXISTS(SELECT C.*
FROM syscomments C,
sysobjects O
WHERE O.id = C.id
AND O.type = ''Tr''
AND C.encrypted = 1)
BEGIN
PRINT ''/*''
PRINT ''The following encrypted triggers were found''
PRINT ''The procedure could not write the script for it''
SELECT DISTINCT A.NAME,
B.NAME,
B.id
FROM dbo.sysusers A,
dbo.sysobjects B,
dbo.syscomments C
WHERE A.uid = B.uid
AND B.type = ''Tr''
AND B.id = C.id
AND C.encrypted = 1
PRINT ''*/''
END
DECLARE ms_crs_syscom CURSOR local forward_only FOR
SELECT T.objid,
C.text
FROM @Triggers T,
dbo.syscomments C
WHERE T.objid = C.id
ORDER BY T.objid,
C.colid
FOR READ only
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
SET @OldTrigID = -1
FETCH next FROM ms_crs_syscom INTO @ObjID, @SyscomText
WHILE @@fetch_status = 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = Len(@SyscomText)
IF @ObjID <> @OldTrigID
BEGIN
SET @LineID = 1
SET @OldTrigID = @ObjID
END
WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = Charindex(Char(13) + Char(10), @SyscomText,
@BasePos)
--If carriage return found
IF @CurrentPos != 0
BEGIN
WHILE ( Isnull(Len(@Line), 0) + @BlankSpaceAdded
+ @CurrentPos - @BasePos + @LFCR ) >
@DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (
Isnull(Len(@Line),
0
) +
@BlankSpaceAdded )
INSERT @TrigText
VALUES ( @ObjID,
@LineId,
Isnull(@Line, N'''')
+ Isnull(Substring(@SyscomText, @BasePos,
@AddOnLen),
N''''))
SELECT @Line = NULL,
@LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen,
@BlankSpaceAdded = 0
END
SELECT @Line = Isnull(@Line, N'''')
+ Isnull(Substring(@SyscomText, @BasePos,
@CurrentPos
-@BasePos +
@LFCR),
N'''')
SELECT @BasePos = @CurrentPos + 2
INSERT @TrigText
VALUES( @ObjID,
@LineId,
@Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
WHILE ( Isnull(Len(@Line), 0) + @BlankSpaceAdded
+ @TextLength - @BasePos + 1 ) >
@DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (
Isnull(Len(@Line),
0
) +
@BlankSpaceAdded )
INSERT @TrigText
VALUES ( @ObjID,
@LineId,
Isnull(@Line, N'''')
+ Isnull(Substring(@SyscomText,
@BasePos,
@AddOnLen),
N''''))
SELECT @Line = NULL,
@LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen,
@BlankSpaceAdded = 0
END
SELECT @Line = Isnull(@Line, N'''')
+ Isnull(Substring(@SyscomText,
@BasePos,
@TextLength
-@BasePos+1
), N'''')
IF Len(@Line) < @DefinedLength
AND Charindex('' '', @SyscomText, @TextLength + 1)
> 0
BEGIN
SELECT @Line = @Line + '' '',
@BlankSpaceAdded = 1
END
END
END
END
FETCH next FROM ms_crs_syscom INTO @ObjID, @SyscomText
END
IF @Line IS NOT NULL
INSERT @TrigText
VALUES( @ObjID,
@LineId,
@Line )
CLOSE ms_crs_syscom
PRINT ''-- You should run this result under dbo if your triggers belong to multiple users''
PRINT ''''
IF @InclDrop = 1
BEGIN
PRINT ''-- Dropping the Triggers''
PRINT ''''
SELECT ''If exists(Select * from sysObjects where id =Object_ID(''''[''
+ username + ''].['' + trigname
+ '']'''') and ObjectProperty(Object_ID(''''[''
+ username + ''].['' + trigname + '']''''), ''''ISTRIGGER'''')=1) Drop Trigger [''
+ username + ''].['' + trigname + ''] '' + Char(13)
+ Char(10) + ''GO'' + Char(13) + Char(10) + Char(13)
+ Char(10)
FROM @Triggers
END
PRINT ''----------------------------------------------''
PRINT ''-- Creation of Triggers''
PRINT ''''
PRINT ''''
DECLARE ms_users CURSOR local forward_only FOR
SELECT T.username,
T.objid,
Max(D.lineid)
FROM @Triggers T,
@TrigText D
WHERE T.objid = D.objid
GROUP BY T.username,
T.objid
FOR READ only
OPEN ms_users
FETCH next FROM ms_users INTO @UserName, @ObjID, @MaxID
WHILE @@fetch_status = 0
BEGIN
PRINT ''SetUser N'''''' + @UserName + '''''''' + Char(13)
+ Char(10)
SELECT ''-- Text of the Trigger''= CASE lineid
WHEN 1 THEN ''GO'' + Char(13) + Char(
10)
+
linetext
WHEN @MaxID THEN linetext + ''GO''
ELSE linetext
END
FROM @TrigText
WHERE objid = @ObjID
ORDER BY lineid
PRINT ''Setuser''
FETCH next FROM ms_users INTO @UserName, @ObjID, @MaxID
END
CLOSE ms_users
PRINT ''GO''
PRINT ''------End ------''
DEALLOCATE ms_crs_syscom
DEALLOCATE ms_users
GO
Cómo ejecutarlo:
SET nocount ON
DECLARE @return_value INT
EXEC @return_value = [dbo].[Createscriptofalltriggers] @InclDrop = 1
SELECT ''Return Value'' = @return_value
IR
Sé que la respuesta ya se ha aceptado, pero quiero ofrecer otra solución para casos en los que, por alguna razón, el asistente SSMS no puede generar secuencias de comandos para desencadenantes (en mi caso fue MSSQL2008R2)
Esta solución se basa en la idea de dana anterior, pero utiliza ''sql_modules'' para proporcionar el código completo del desencadenante si supera los 4000 caracteres (restricción de la columna ''texto'' de la vista ''syscomments'')
select [definition],''GO'' from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type =''TR''
Haga clic derecho en la cuadrícula de resultados y luego "Guardar resultados como ..." guarda en el archivo con formato preservado
el uso de syscomments puede no funcionar siempre. La columna de texto de syscomments está limitada a 4000 caracteres. Un disparador que exceda eso será truncado. Es posible que no pueda verlo por completo.
Base de datos-> Tareas-> Generar guiones -> Siguiente -> Siguiente
En la interfaz de usuario de elegir opciones de script , en el encabezado Opciones de tabla / vista, configure los activadores de guiones en True .