Delete a database for all trigger and SP in SQL

SQLDelete all trigger and SP from a database

Author : CC Abba

2014-6-14

in Daily SQL database operation, how to quickly delete all Trigger and SP ?

There are three ways to handle this quickly.

-- First Kind

-- How transactions are handled

Begin Transaction

Begin try

Declare @SQL varchar(max)

Set @SQL="

select Span style= "Font-size:10.0pt;font-family:"courier new";" > @SQL = @SQL Span style= "Color:gray" >+ name + from sysobjects where xtype = ' TR ' and name <> ' dropdatabase '

If ISNULL(@SQL,')! ='

Begin

Set @SQL=' Drop Trigger '+left (@SQL,len( @SQL )-1)

Select @SQL as aa

--exec (@SQL)

End

Commit Transaction

End Try

Begin Catch

rollback Tran

End Catch

-- The second method of

-- the way the cursor is used

--declare cursorname cursor for select ' Drop PROCEDURE ' +name from sys.objects where name like ' xx% ' and xtype = ' P ' -- Delete the corresponding stored procedure

DECLARE cursorname cursor for select ' Drop Trigger '+name from SYS . Objects where name like '% ' and type = ' TR ' --delete the corresponding trigger

Open cursorname

Declare @curname sysname

Fetch next from cursorname to @curname

while (@ @fetch_status=0)

begin

--exec (@curname)

Select @curname as AA

Fetch next from cursorname to @curname

End

Close cursorname

deallocate cursorname

-- The third method of

-- Simple method, after querying, then execute

in the database

select Span style= "Font-size:10.0pt;font-family:"courier new";" > ' drop Trigger ' + name from sys objects where name like and type = ' TR '

select Span style= "Font-size:10.0pt;font-family:"courier new";" > ' drop PROCEDURE ' + name from sys objects where name like and type = ' P '