开发库测试库间触发器同步 查寻某时间点后的触发器 生成其删除 创建 禁用的脚本

declare @trigname nvarchar(100)=N'';
--查找某时间点后的增修的Trigger
declare curProc cursor for
select 
 [name]
FROM 
 sys.all_objects 
where 
 type_desc = N'SQL_TRIGGER' 
 and is_ms_shipped = 0
 and substring([name],1,4)  in ('TRG_')
 and modify_date >='2017-08-17 00:00:00'
order by 
 modify_date desc;

open curProc;
fetch next from curProc into @trigname;
while @@FETCH_STATUS = 0 
begin
 --如存在 则删除
 print(N'IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE [NAME]=N''' + @trigname +''' AND [TYPE]=''TR'')')
 PRINT(N'DROP TRIGGER ' + @trigname);
 print N'GO';

 --重建
 exec ('sp_helptext ' + @trigname);
 print N'GO';

 fetch next from curProc into @trigname;
end
close curProc;
deallocate curProc;



declare @trigname2 nvarchar(100)=N'';
declare @tablename2 nvarchar(100)=N'';
--查找当前表及触发器的禁用状态  仅过滤需要禁用的
declare curProc2 cursor for
SELECT
[Name] as TriggerName,
object_name(parent_object_id) as TableName 
FROM sys.all_objects
WHERE  [TYPE]='TR'
 and  is_ms_shipped = 0
 and modify_date >='2017-08-10 00:00:00'
 and 1=OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')  
;

open curProc2;
fetch next from curProc2 into @trigname2, @tablename2;
while @@FETCH_STATUS = 0 
begin
  --按需 禁用 
 print N'';
 PRINT(N'ALTER TABLE ' + @tablename2 +' DISABLE  TRIGGER  ' + @trigname2) +N' ; ';
 print N'GO';
 
 fetch next from curProc2 into @trigname2, @tablename2;
end
close curProc2;
deallocate curProc2;