Ram,

If you create the procedure in the master database then you don't need to pass the db name through to it because select * from sysobjects will automatically select from the database you're currently logged into.

Note that you also need the owner name to delete the trigger as you cannot assume that the owner will be "dbo".

To execute this, just change the database and run exec sp_DropAllTriggers.

Note that I have commented out the sp_executesql call and I've added a couple of print statements just to verify I'm going to be deleting what I should be deleting.

CREATE PROCEDURE sp_DropAllTriggers

AS

DECLARE @SqlCmd VARCHAR(8000)

declare @Trig sysname

declare @owner sysname

declare @uid int

DECLARE TGCursor CURSOR FOR

SELECT name, uid FROM sysobjects WHERE type = 'TR'

OPEN TGCursor

FETCH next FROM TGCursor INTO @Trig, @uid

WHILE @@FETCH_STATUS = 0

BEGIN

set @SQLCmd = 'drop trigger [' + user_name(@uid) + '].[' + @Trig + ']'

--exec sp_executesql @SQLCmd

print 'for testing!'

print ''

print @SQLCmd

FETCH next FROM TGCursor INTO @Trig, @uid

END

CLOSE TGCursor

DEALLOCATE TGCursor

GO