Drop all user objects

Here is a script to drop all of the user-defined objects in a database. Useful when one of your co-workers runs a script against the master database to create 600 tables and 1000 stored procedures. The script won’t handle every object just yet, the unsupported objects will have it’s name plus an underscore prefix.
— Drop all user created objects in a database.
— Drop foreign keys first, functions and types last.

set nocount on

declare Diff_Cursor cursor

select case when [XType] = ‘F’ then ‘alter table [‘ + object_name(parent_obj) + ‘] ‘ + 
    ‘drop constraint [‘ + [name] + ‘]’
 when [XType] = ‘U’ then ‘drop table [‘ + [name] + ‘]’
 when [XType] = ‘P’ then ‘drop procedure [‘ + [name] + ‘]’
 when [XType] = ‘V’ then ‘drop view [‘ + [name] + ‘]’
 when [XType] in (‘FN’, ‘TF’, ‘FT’) then ‘drop function [‘ + [name] + ‘]’
 else ‘_’ + [XType] + ‘:’ + [name] end as DropSQL,
 case when [XType] in (‘FN’, ‘TF’) then 2
 when [XType] = ‘F’ then 0
 else 1 end as SortOrder
from sysobjects
where objectproperty([ID], ‘IsMSShipped’) = 0 and
 [XType] not in (‘D’, ‘PK’, ‘C’, ‘UQ’, ‘TR’)
union all
select ‘drop type [‘ + [name] + ‘]’ as DropSQL, 2 as SortOrder
from sys.types
where is_user_defined = 1
order by 2

declare @SQL nvarchar(4000)
declare @SortOrder tinyint

open Diff_Cursor

fetch next from Diff_Cursor into @SQL, @SortOrder

while @@FETCH_STATUS = 0

 print @SQL
 –exec sp_executesql @SQL

 fetch next from Diff_Cursor into @SQL, @SortOrder

close Diff_Cursor

deallocate Diff_Cursor


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: