· 8 years ago · Apr 17, 2018, 08:14 PM
1-- script all triggers of the current database
2--marcelo miorelli
3--17-april-2018
4DECLARE @CHECK_IF_TRIGGER_EXISTS BIT = 1
5
6SET NOCOUNT ON
7SET DEADLOCK_PRIORITY LOW
8SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
9
10IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL
11 BEGIN
12 DROP TABLE #RADHE
13 END
14
15CREATE TABLE #Radhe(
16 DB sysname not null,
17 parent_name nvarchar(600) not null,
18 object_id int not null,
19 trigger_name sysname not null,
20 is_disabled bit,
21 i int not null identity(1,1),
22 [trigger_definition] NVARCHAR(MAX) not null
23);
24
25DECLARE @object_id int;
26DECLARE @SQL nvarchar(max);
27DECLARE @theSQL nvarchar(max);
28declare @DB sysname
29declare @parent_name nvarchar(600)
30declare @trigger_name sysname
31declare @is_disabled bit
32
33
34 select @DB = db_name(db_id())
35 --select @DB
36
37
38
39 SELECT @SQL =
40 '-------------------------------------------------------------------------------------------------------------------
41 DECLARE
42 @olddelim nvarchar(32) = char(13) + Char(10),
43 @newdelim nchar(1) = NCHAR(9999); -- pencil (âœ)
44
45 SELECT the_db_name = quotename(db_name(db_id()))
46 ,parent_name = @parent_name
47 ,the_object_id = @obj_id
48 ,trigger_name = @trigger_name
49 ,is_disabled = @is_disabled
50 ,trigger_definition = [Value]
51 FROM master.dbo.splitstring(OBJECT_DEFINITION(@obj_id), @olddelim);
52 -------------------------------------------------------------------------------------------------------------------';
53
54-- PRINT @SQL
55
56
57
58
59BEGIN TRY
60
61 DECLARE the_triggers CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
62 FOR
63 SELECT
64 object_id=s.object_id
65 ,parent_name = QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id))
66 ,trigger_name = QUOTENAME(s.name)
67 ,s.is_disabled
68 FROM sys.triggers s
69 WHERE 1=1
70
71
72 OPEN the_triggers;
73 FETCH NEXT FROM the_triggers
74 INTO @object_id,@parent_name,@trigger_name,@is_disabled;
75
76 WHILE @@FETCH_STATUS = 0
77 BEGIN
78
79 SET @theSQL = 'EXEC ' + QUOTENAME(@DB) +
80 '.sys.sp_executesql @SQL' + CHAR(10) +
81 ',N''@obj_id int,@parent_name nvarchar(600),@trigger_name sysname,@is_disabled bit'',' + CHAR(10) +
82 '''' + CAST (@object_id as nvarchar) + '''' + ',' +
83 '''' + @parent_name + '''' + ',' +
84 '''' + @trigger_name + '''' + ',' +
85 '''' + CAST (COALESCE(@is_disabled,0) as nvarchar) +
86 '''' +';' + CHAR(10)
87
88 -------------------------------------------
89 -- when @CHECK_IF_TRIGGER_EXISTS is on
90 -- add code that checks whether the trigger exists
91 -- and if it does drop it
92 -------------------------------------------
93
94 if @CHECK_IF_TRIGGER_EXISTS = 1
95 BEGIN
96
97 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
98 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
99
100 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
101 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'use ' + QUOTENAME(db_name()))
102
103 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
104 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
105
106 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
107 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'if OBJECT_ID('+ @trigger_name + ') is not null')
108
109 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
110 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,' drop trigger '+ @trigger_name + ' ')
111
112 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
113 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
114
115
116 END
117
118 -------------------------------------------
119 -- do the insert here
120 -- the trigger source code
121 -------------------------------------------
122
123 --print @theSQL
124
125 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition)
126 EXEC sys.sp_executesql @theSQL
127 , N'@SQL nvarchar(max) '
128 , @SQL = @sql
129
130 -- add a GO after the trigger definition
131 INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
132 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
133
134 -- fetch the next trigger
135 FETCH NEXT FROM the_triggers
136 INTO @object_id,@parent_name,@trigger_name,@is_disabled;
137
138 END
139
140 -------------------------------------------
141 BEGIN TRY
142 --clean it up
143 CLOSE the_triggers;
144 DEALLOCATE the_triggers;
145 END TRY
146 BEGIN CATCH
147 --do nothing
148 END CATCH
149 -------------------------------------------
150
151END TRY
152
153BEGIN CATCH
154
155 -------------------------------------------
156 BEGIN TRY
157 --clean it up
158 CLOSE the_triggers;
159 DEALLOCATE the_triggers;
160 END TRY
161 BEGIN CATCH
162 --do nothing
163 END CATCH
164 -------------------------------------------
165
166 DECLARE @ERRORMESSAGE NVARCHAR(512),
167 @ERRORSEVERITY INT,
168 @ERRORNUMBER INT,
169 @ERRORSTATE INT,
170 @ERRORPROCEDURE SYSNAME,
171 @ERRORLINE INT,
172 @XASTATE INT
173
174 SELECT
175 @ERRORMESSAGE = ERROR_MESSAGE(),
176 @ERRORSEVERITY = ERROR_SEVERITY(),
177 @ERRORNUMBER = ERROR_NUMBER(),
178 @ERRORSTATE = ERROR_STATE(),
179 @ERRORPROCEDURE = ERROR_PROCEDURE(),
180 @ERRORLINE = ERROR_LINE()
181
182 SET @ERRORMESSAGE =
183 (
184 SELECT CHAR(13) +
185 'Message:' + SPACE(1) + @ErrorMessage + SPACE(2) + CHAR(13) +
186 'Error:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber) + SPACE(1) + CHAR(13) +
187 'Severity:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity) + SPACE(1) + CHAR(13) +
188 'State:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState) + SPACE(1) + CHAR(13) +
189 'Routine_Name:' + SPACE(1) + COALESCE(@ErrorProcedure,'') + SPACE(1) + CHAR(13) +
190 'Line:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine) + SPACE(1) + CHAR(13) +
191 'Executed As:' + SPACE(1) + SYSTEM_USER + SPACE(1) + CHAR(13) +
192 'Database:' + SPACE(1) + DB_NAME() + SPACE(1) + CHAR(13) +
193 'OSTime:' + SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121) + CHAR(13)
194 )
195
196 --We can also save the error details to a table for later reference here.
197 RAISERROR (@ERRORMESSAGE,16,1)
198
199END CATCH
200
201SELECT * FROM #RADHE
202
203CREATE TABLE #tmp
204(
205 db sysname,
206 sch sysname,
207 obj sysname,
208 name sysname,
209 is_disabled bit,
210 def nvarchar(max)
211);
212GO
213
214INSERT #tmp SELECT DB_NAME(),
215 s.name, o.name, t.name,
216 t.is_disabled, m.definition
217FROM sys.triggers AS t
218INNER JOIN sys.sql_modules AS m
219ON t.object_id = m.object_id
220INNER JOIN sys.objects AS o
221ON t.parent_id = o.object_id
222INNER JOIN sys.schemas AS s
223ON o.schema_id = s.schema_id
224WHERE parent_class = 1;
225
226DECLARE @sql nvarchar(max) = N'';
227
228SELECT @sql += def
229 + CHAR(13) + CHAR(10) + N'GO'
230 + CHAR(13) + CHAR(10)
231FROM #tmp;
232
233SELECT @sql += N'DISABLE TRIGGER '
234 + QUOTENAME(sch) + N'.' + QUOTENAME(name)
235 + N' ON '
236 + QUOTENAME(sch) + N'.' + QUOTENAME(obj) + N';'
237FROM #tmp WHERE is_disabled = 1;
238
239PRINT @sql;
240-- EXEC sys.sp_executesql @sql;
241
242DECLARE @db sysname = N'AdventureWorks';
243
244DECLARE @exec nvarchar(max), @sql nvarchar(max);
245
246SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';
247
248SET @sql = N'SELECT DB_NAME();';
249
250EXEC @exec @sql;