Skip to main content
formatted the text
Source Link
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311

For DML triggers you can use below cursor. If you need to disable DDL triggers as well, you will need to create similar cursor base on sys.triggers view. (as in sys.objects we have only DML cursors)

declare @sql nvarchar(150)

declare trigger_cursor CURSOR FOR
select  'DISABLE TRIGGER ' + SCHEMA_NAME(schema_id)+'.'+name 
       +' ON ' +SCHEMA_NAME'+SCHEMA_NAME(schema_id)+'.'+ OBJECT_NAME(parent_object_id)
    from sys.objects
    where type = 'TR'


OPEN trigger_cursor  
FETCH NEXT FROM trigger_cursor INTO @sql 


WHILE @@FETCH_STATUS = 0  
BEGIN  
      print @sql
      --sp_executesql @sql 
      FETCH NEXT FROM trigger_cursor INTO @sql 
END 

CLOSE trigger_cursor  
DEALLOCATE trigger_cursor 

For DML triggers you can use below cursor. If you need to disable DDL triggers as well, you will need to create similar cursor base on sys.triggers view. (as in sys.objects we have only DML cursors)

declare @sql nvarchar(150)

declare trigger_cursor CURSOR FOR
select  'DISABLE TRIGGER ' + SCHEMA_NAME(schema_id)+'.'+name +' ON ' +SCHEMA_NAME(schema_id)+'.'+ OBJECT_NAME(parent_object_id)
    from sys.objects
    where type = 'TR'


OPEN trigger_cursor  
FETCH NEXT FROM trigger_cursor INTO @sql 


WHILE @@FETCH_STATUS = 0  
BEGIN  
      print @sql
      --sp_executesql @sql 
      FETCH NEXT FROM trigger_cursor INTO @sql 
END 

CLOSE trigger_cursor  
DEALLOCATE trigger_cursor 

For DML triggers you can use below cursor. If you need to disable DDL triggers as well, you will need to create similar cursor base on sys.triggers view. (as in sys.objects we have only DML cursors)

declare @sql nvarchar(150)

declare trigger_cursor CURSOR FOR
select  'DISABLE TRIGGER ' + SCHEMA_NAME(schema_id)+'.'+name 
       +' ON '+SCHEMA_NAME(schema_id)+'.'+ OBJECT_NAME(parent_object_id)
    from sys.objects
    where type = 'TR'


OPEN trigger_cursor  
FETCH NEXT FROM trigger_cursor INTO @sql 


WHILE @@FETCH_STATUS = 0  
BEGIN  
      print @sql
      --sp_executesql @sql 
      FETCH NEXT FROM trigger_cursor INTO @sql 
END 

CLOSE trigger_cursor  
DEALLOCATE trigger_cursor 

Source Link

For DML triggers you can use below cursor. If you need to disable DDL triggers as well, you will need to create similar cursor base on sys.triggers view. (as in sys.objects we have only DML cursors)

declare @sql nvarchar(150)

declare trigger_cursor CURSOR FOR
select  'DISABLE TRIGGER ' + SCHEMA_NAME(schema_id)+'.'+name +' ON ' +SCHEMA_NAME(schema_id)+'.'+ OBJECT_NAME(parent_object_id)
    from sys.objects
    where type = 'TR'


OPEN trigger_cursor  
FETCH NEXT FROM trigger_cursor INTO @sql 


WHILE @@FETCH_STATUS = 0  
BEGIN  
      print @sql
      --sp_executesql @sql 
      FETCH NEXT FROM trigger_cursor INTO @sql 
END 

CLOSE trigger_cursor  
DEALLOCATE trigger_cursor