Dropping Views, Procedures and Fucntions from a Database at a time.


Take into account that this script just generates the script to delete objects and that it considers only default schema of objects. You can easily modify script to use different schemas.

SET NOCOUNT ON

declare sps cursor for

select name

from sysobjects

where type = ‘P’

  and objectproperty(id, ‘IsProcedure’) = 1

  and objectproperty(id, ‘IsReplProc’) = 0

  and objectproperty(id, ‘IsMSShipped’) = 0

open sps

declare @spname varchar(100)

fetch next from sps into @spname

while @@fetch_status = 0

begin

print (‘drop procedure ‘ + @spname + ‘;’)

fetch next from sps into @spname

end

close sps

deallocate sps

declare funcs cursor for

select name

from sysobjects

where type in (‘TF’, ‘IF’, ‘FN’)

  and objectproperty(id, ‘IsMSShipped’) = 0

open funcs

declare @funcname varchar(100)

fetch next from funcs into @funcname

while @@fetch_status = 0

begin

print(‘drop function ‘ + @funcname + ‘;’)

fetch next from funcs into @funcname

end

close funcs

deallocate funcs

declare vw cursor for

select name

from sysobjects

where type in (‘V’)

  and category = 0

  and [name] not like ‘sys%’

  and objectproperty(id, ‘IsMSShipped’) = 0

open vw

declare @viewname varchar(100)

fetch next from vw into @viewname

while @@fetch_status = 0

begin

set @viewname = QUOTENAME(@viewname)

print(‘drop view ‘ + @viewname + ‘;’)

fetch next from vw into @viewname

end

close vw

deallocate vw

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: