How to drop all schema objects in MS SQL 2005

I needed to test my SQL implementation script that was a part of big SQL Server Data Warehouse release. For anybody who have ever done large SQL implementation fundamental question is always "Have I scripted everything that is needed?". So how to check if your implementation script contains all objects you require? Here is a few steps that helped me:

1. Collate all individual scripts into one or more implementation script (I recommend having  one script per development, but it always depends on circumstances).


2. Order your scripts so you know the sequence of running them. This is specifically important if there is dependency between objects created by different scripts.


3. Create backout script for each implementation one and order them as well.


4. Drop all objects that come into release. "How to drop all schema objects in one go?" - this is where it turns out to be priceless to have DBA in your team:

declare @schema varchar(200)
select @schema = 'MySchema'

select
'DROP ' + case
    when o.xtype = 'U' then 'TABLE'
    when o.xtype = 'V' then 'VIEW'
    when o.xtype = 'P' then 'PROCEDURE'
    when o.xtype = 'FN' then 'FUNCTION'
    end + ' ' + s.name + '.' + o.name as SQL
from
    sys.sysobjects as o
    join sys.schemas as s on o.uid = s.schema_id
where
    s.name = @schema and
    o.xtype in ('U','V','P','FN')
union all
select
'DROP SCHEMA ' + @schema

This will give you DROP statement for each table, view, stored procedure and user defined function in your schema, as well as schema itself. Just grab whichever statement you need and execute it!