Here is the completed stored procedure. I had problems with @SQLCmd variable type being VARCHAR, so changed it to NVARCHAR.

USE master

go

IF EXISTS (SELECT * FROM sysobjects

WHERE id = object_id(N'[dbo].[sp_DropAllTriggers]')

AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[sp_DropAllTriggers]

GO

CREATE PROCEDURE dbo.sp_DropAllTriggers

AS

DECLARE @SQLCmd nvarchar(1000)

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 = N'DROP TRIGGER [' + user_name(@uid) + '].[' + @Trig + ']'

EXEC sp_executesql @SQLCmd

PRINT @SQLCmd

FETCH next FROM TGCursor INTO @Trig, @uid

END

CLOSE TGCursor

DEALLOCATE TGCursor

GO