I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.
Every time I need to reinitialise those database I need to script the triggers, permissions and indexes, apply the snapshot and then re-apply those triggers, indexes and permissions.
Is there a script that I can use to script all the triggers of a current database?
This seems a whole lot simpler than what you have:
CREATE TABLE #tmp
(
db sysname,
sch sysname,
obj sysname,
name sysname,
is_disabled bit,
def nvarchar(max)
);
GO
INSERT #tmp SELECT DB_NAME(),
s.name, o.name, t.name,
t.is_disabled, m.definition
FROM sys.triggers AS t
INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
INNER JOIN sys.objects AS o
ON t.parent_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE parent_class = 1;
When you're ready to create them again:
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += def
+ CHAR(13) + CHAR(10) + N'GO'
+ CHAR(13) + CHAR(10)
FROM #tmp;
SELECT @sql += N'DISABLE TRIGGER '
+ QUOTENAME(sch) + N'.' + QUOTENAME(name)
+ N' ON '
+ QUOTENAME(sch) + N'.' + QUOTENAME(obj) + N';'
FROM #tmp WHERE is_disabled = 1;
PRINT @sql;
-- EXEC sys.sp_executesql @sql;
Yes, you won't be able to validate the entire script is there, because SSMS won't output the entire value of @sql. See this post for workarounds.
Also, I don't know why you need the database as part of the dynamic script if you always know the database you're affecting, but there's a much safer way to make the database name dynamic without concatenating values and inviting SQL injection:
DECLARE @db sysname = N'AdventureWorks';
DECLARE @exec nvarchar(max), @sql nvarchar(max);
SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';
SET @sql = N'SELECT DB_NAME();';
EXEC @exec @sql;
Please find below a script that has helped me a lot:
-- script all triggers of the current database
--marcelo miorelli
--17-april-2018
DECLARE @CHECK_IF_TRIGGER_EXISTS BIT = 1
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL
BEGIN
DROP TABLE #RADHE
END
CREATE TABLE #Radhe(
DB sysname not null,
parent_name nvarchar(600) not null,
object_id int not null,
trigger_name sysname not null,
is_disabled bit,
i int not null identity(1,1),
[trigger_definition] NVARCHAR(MAX) not null
);
DECLARE @object_id int;
DECLARE @SQL nvarchar(max);
DECLARE @theSQL nvarchar(max);
declare @DB sysname
declare @parent_name nvarchar(600)
declare @trigger_name sysname
declare @is_disabled bit
select @DB = db_name(db_id())
--select @DB
SELECT @SQL =
'-------------------------------------------------------------------------------------------------------------------
DECLARE
@olddelim nvarchar(32) = char(13) + Char(10),
@newdelim nchar(1) = NCHAR(9999); -- pencil (✏)
SELECT the_db_name = quotename(db_name(db_id()))
,parent_name = @parent_name
,the_object_id = @obj_id
,trigger_name = @trigger_name
,is_disabled = @is_disabled
,trigger_definition = [Value]
FROM master.dbo.splitstring(OBJECT_DEFINITION(@obj_id), @olddelim);
-------------------------------------------------------------------------------------------------------------------';
-- PRINT @SQL
BEGIN TRY
DECLARE the_triggers CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
object_id=s.object_id
,parent_name = QUOTENAME(OBJECT_SCHEMA_NAME(s.parent_id)) + '.' + QUOTENAME(OBJECT_NAME(s.parent_id))
,trigger_name = QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id))
,s.is_disabled
FROM sys.triggers s
WHERE 1=1
OPEN the_triggers;
FETCH NEXT FROM the_triggers
INTO @object_id,@parent_name,@trigger_name,@is_disabled;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @theSQL = 'EXEC ' + QUOTENAME(@DB) +
'.sys.sp_executesql @SQL' + CHAR(10) +
',N''@obj_id int,@parent_name nvarchar(600),@trigger_name sysname,@is_disabled bit'',' + CHAR(10) +
'''' + CAST (@object_id as nvarchar) + '''' + ',' +
'''' + @parent_name + '''' + ',' +
'''' + @trigger_name + '''' + ',' +
'''' + CAST (COALESCE(@is_disabled,0) as nvarchar) +
'''' +';' + CHAR(10)
-------------------------------------------
-- when @CHECK_IF_TRIGGER_EXISTS is on
-- add code that checks whether the trigger exists
-- and if it does drop it
-------------------------------------------
if @CHECK_IF_TRIGGER_EXISTS = 1
BEGIN
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'use ' + QUOTENAME(db_name()))
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'if OBJECT_ID('+ '''' + @trigger_name + '''' + ') is not null')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,' drop trigger '+ @trigger_name + ' ')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
END
-------------------------------------------
-- do the insert here
-- the trigger source code
-------------------------------------------
--print @theSQL
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition)
EXEC sys.sp_executesql @theSQL
, N'@SQL nvarchar(max) '
, @SQL = @sql
-- add a GO after the trigger definition
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
-- fetch the next trigger
FETCH NEXT FROM the_triggers
INTO @object_id,@parent_name,@trigger_name,@is_disabled;
END
-------------------------------------------
BEGIN TRY
--clean it up
CLOSE the_triggers;
DEALLOCATE the_triggers;
END TRY
BEGIN CATCH
--do nothing
END CATCH
-------------------------------------------
END TRY
BEGIN CATCH
-------------------------------------------
BEGIN TRY
--clean it up
CLOSE the_triggers;
DEALLOCATE the_triggers;
END TRY
BEGIN CATCH
--do nothing
END CATCH
-------------------------------------------
DECLARE @ERRORMESSAGE NVARCHAR(512),
@ERRORSEVERITY INT,
@ERRORNUMBER INT,
@ERRORSTATE INT,
@ERRORPROCEDURE SYSNAME,
@ERRORLINE INT,
@XASTATE INT
SELECT
@ERRORMESSAGE = ERROR_MESSAGE(),
@ERRORSEVERITY = ERROR_SEVERITY(),
@ERRORNUMBER = ERROR_NUMBER(),
@ERRORSTATE = ERROR_STATE(),
@ERRORPROCEDURE = ERROR_PROCEDURE(),
@ERRORLINE = ERROR_LINE()
SET @ERRORMESSAGE =
(
SELECT CHAR(13) +
'Message:' + SPACE(1) + @ErrorMessage + SPACE(2) + CHAR(13) +
'Error:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber) + SPACE(1) + CHAR(13) +
'Severity:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity) + SPACE(1) + CHAR(13) +
'State:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState) + SPACE(1) + CHAR(13) +
'Routine_Name:' + SPACE(1) + COALESCE(@ErrorProcedure,'') + SPACE(1) + CHAR(13) +
'Line:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine) + SPACE(1) + CHAR(13) +
'Executed As:' + SPACE(1) + SYSTEM_USER + SPACE(1) + CHAR(13) +
'Database:' + SPACE(1) + DB_NAME() + SPACE(1) + CHAR(13) +
'OSTime:' + SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121) + CHAR(13)
)
--We can also save the error details to a table for later reference here.
RAISERROR (@ERRORMESSAGE,16,1)
END CATCH
SELECT * FROM #RADHE