Drop all Triggers belonging to any schema in SQL Server

-

How to drop all triggers in a SQL Server database:



Drop all Triggers belonging to any schema :

In this post we want to delete all triggers from specific sql database. For this we use cursor . if you don’t know about sql server cursor then visit fallowing post:

·    Sql server cursor declaration syntax example

·    Sql server –Example of sql cursor with syntax

Drop Triggers from SQL Server databse:

Declare my_cursor Cursor For Select [name] From sys.objects where type = 'tr'

Open my_cursor

Fetch Next From cur Into @trgName

      While @@fetch_status = 0

        Begin

             -- for delete or drop all trigger from database.
               Exec('drop trigger ' + @trgName)

             -- find the newxt trigger
              Fetch Next From my_cursor Into @trgName

        End

      Close my_cursor

            -- After complete the work relise the memory occupied by cursor.

Deallocate my_cursor



Sql Server Interview Qus:

Comments

Post a Comment