DeleteOnlyCode.sql - SQL Server 2005

declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = ‘p’ AND (NAME LIKE ‘USP_%’ OR NAME LIKE ‘cv_%’ )
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec(‘drop procedure ‘ + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur



declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = ‘tr’ AND (NAME LIKE ‘trg_%’ )
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec(‘drop trigger ‘ + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur



declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where (type = ‘fn’ or type = ‘if’ or type = ‘tf’) AND (NAME LIKE ‘ufn_%’ )
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec(‘drop function ‘ + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur

• Couldn’t find a definite way of differentiating between system and user created objects so going by naming conventions.
• Most of the codes on the net don’t even bother. If you know one, leave a comment please