CODEWITHSUNDEEP
sql-servert-sqltriggers
phicon
phicon

Reputation: 3617

SQL Server - Drop all triggers from table where trigger names are unknown

I am using SignalR which automatically creates triggers, with random names, on a database table.

Is there a way of using T-SQL script to DROP all triggers from a specific table without dropping the table?

Upvotes: 0

Views: 2053

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You could use dynamic SQL and sys.triggers system table:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(FORMATMESSAGE(N'DROP TRIGGER %s.%s;'
                                      ,QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id))
                                      ,QUOTENAME(t.name))
                        ,NCHAR(13))
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
  AND t.parent_id = OBJECT_ID('table_name');

PRINT @sql;  -- debug
--EXEC(@sql);

db<>fiddle demo

EDIT:

SQL Server 2012 version:

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += FORMATMESSAGE(N'DROP TRIGGER %s.%s;'
                                      ,QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id))
                                      ,QUOTENAME(t.name))
                        + NCHAR(13)
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
  AND t.parent_id = OBJECT_ID('t');

PRINT @sql;  -- debug
--EXEC(@sql);

db<>fiddle demo

Upvotes: 3

Related Questions