To query all triggers of a database, use:
SELECT * FROM sys.triggers
I've tried delete all or some of triggers contained in the result:
DELETE FROM sys.triggers or DELETE FROM sys.triggers WHERE nome LIKE 'doc%'
But you acknowledge the error :
Ad hoc updates to system catalogs are not allowed.
A more "simple" way of doing this will be as follows:
DECLARE @SQL AS NVARCHAR(MAX) = ''
SELECT @SQL = @SQL + 'DROP TRIGGER ' + [name]
FROM sys.triggers
WHERE [name] LIKE 'COP%'
EXEC(@SQL)
DELETE FROM sys.triggers This will try to delete the records from a system table, not the triggers.
To delete a trigger the command is:
DROP TRIGGER nome
Documentation: DROP TRIGGER a>
Here is a script that can delete several by setting the command DROP and then using EXEC to delete:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql +=
N'DROP TRIGGER ' +
QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' +
QUOTENAME(t.name) + N'; ' + NCHAR(13)
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
AND t.parent_class_desc = N'OBJECT_OR_COLUMN';
EXEC (@sql);
Copying from dba exchange