Alle Trigger einer Datenbank löschen

Expand / Collapse
 

Alle Trigger einer Datenbank löschen


Zusammenfassung

Das nachfolgende Skript löscht sämtliche Trigger einer Datenbank.

Skript

DECLARE @SQLCmd nvarchar(1000) 
DECLARE @Trig varchar(500)
DECLARE @sch varchar(500)

DECLARE TGCursor CURSOR FOR

SELECT ISNULL(tbl.name, vue.name) AS [schemaName]
, trg.name AS triggerName
FROM sys.triggers trg
LEFT OUTER JOIN (SELECT tparent.object_id, ts.name
FROM sys.tables tparent
INNER JOIN sys.schemas ts ON ts.schema_id = tparent.schema_id)
AS tbl ON tbl.object_id = trg.parent_id
LEFT OUTER JOIN (SELECT vparent.object_id, vs.name
FROM sys.views vparent
INNER JOIN sys.schemas vs ON vs.schema_id = vparent.schema_id)
AS vue ON vue.object_id = trg.parent_id

OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @sch,@Trig
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + @sch + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @sch,@Trig
END

CLOSE TGCursor
DEALLOCATE TGCursor



Rate this Article:

Add Your Comments


Name: *
Email Address:
Web Address:
   
  
 
 
   
Verification Code:
*
 

Details
Last Modified:16.01.2016

Last Modified By: Markus Diettrich

Type: INFO

Article not rated yet.

Article has been viewed 762 times.

Options