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
for
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
begin
print @SQL
–exec sp_executesql @SQL
fetch next from Diff_Cursor into @SQL, @SortOrder
end
close Diff_Cursor
deallocate Diff_Cursor
go