You can use the following stored procedure to drop all triggers on a table:
create procedure drop_all_triggers(@tablename varchar(255)) as begin select so2.name into #deletetriggers from sysobjects so1, sysobjects so2 where (so2.id = so1.deltrig or so2.id = so1.instrig or so2.id=so1.updtrig or so2.id=so1.seltrig) and so1.name=@tablename declare delete_trigger_cursor cursor for select name from #deletetriggers declare @trigger_name varchar(255) declare @selectString varchar(10000) open delete_trigger_cursor fetch delete_trigger_cursor into @trigger_name while @@sqlstatus = 0 begin set @selectString = 'drop trigger '+@trigger_name print @selectString exec (@selectString) fetch delete_trigger_cursor into @trigger_name end close delete_trigger_cursor deallocate cursor delete_trigger_cursor end go
It basically does the following:
- Fetch the name of all triggers on the table into a temp table (we have to go through a temp table since by dropping a trigger we modifying the sysobject table and it would mess with the cursor.
- Loop through the trigger names
- And drop each of them using an execute immediate. Somehow you cannot directly call drop trigger…
Just call it like this:
1> drop_all_triggers mytablename 2> go