Friday, February 7, 2014

Drop the user defined objects

--remove all user defined tables exec sp_MSforeachtable @command1 = 'Drop table ?';
--remove all the user defined stored procedures from the database
declare @procname nvarchar(max)
declare curProc Cursor for SELECT [name] from sys.objects where type = 'P'
Open curProc Fetch next from curProc into @procName
While @@fetch_Status = 0
Begin
Exec('drop procedure ' + @procName)
Fetch Next From curProc Into @procName
End
Close curProc
Deallocate curProc --drop all user defined views

Declare @viewName varchar(500)
Declare curView Cursor For Select [name] From sys.objects where type = 'v'
Open curView Fetch Next From curView Into @viewName
While @@fetch_status = 0
Begin
Exec('drop view [' + @viewName+']')
Fetch Next From curView Into @viewName
End
Close curView
Deallocate curView

--Remove all Triggers
-- drop all user defined triggers
Declare @trgName varchar(500)
Declare curTrig Cursor For Select [name] From sys.objects where type = 'tr'
Open curTrig Fetch Next From curTrig Into @trgName
While @@fetch_status = 0 Begin Exec('drop trigger ' + @trgName)
Fetch Next From curTrig Into @trgName
End
Close curTrig
Deallocate curTrig --Remove all functions -- drop all user defined scalar-valued functions
Declare @FncName varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'FN'
Open curFunc
Fetch Next From curFunc Into @FncName
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName)
Fetch Next From curFunc Into @FncName
End Close curFunc
Deallocate curFunc

-- drop all user defined table-valued functions
Declare @FncName1 varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'TF'

Open curFunc
Fetch Next From curFunc Into @FncName1
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName1)
Fetch Next From curFunc Into @FncName1
End
Close curFunc
Deallocate curFunc

No comments:

Post a Comment