How to drop all tables from a SQL Server 2005 Database

How to drop all tables, all views, and all stored procedures
from a SQL Server 2005 Database?

It may not be a hardcore requirement on day-to-day basis to drop all tables, views and stored
procedures from a SQL Server database within your environment, but it will be handy to have
such a code at your end when such task is required.

There are 2 ways to accomplish this, first using undocumented stored procedure such as
'sp_MSforeachtable' as follows:

exec sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "

Where the results will have all of the tables to be dropped, ok how about
for views & stored procedure then. Here it goes:

create procedure Usp_DropAllSPViews
as

declare @name  varchar(100)
declare @xtype char(1)
declare @sqlstring nvarchar(1000)

declare AllSPViews_cursor cursor for
SELECT sysobjects.name, sysobjects.xtype
FROM sysobjects
  join sysusers on sysobjects.uid = sysusers.uid
where OBJECTPROPERTY(sysobjects.id, N'IsProcedure') = 1
  or OBJECTPROPERTY(sysobjects.id, N'IsView') = 1 and
sysusers.name ='USERNAME'

open AllSPViews_cursor

fetch next from SPViews_cursor into @name, @xtype

while @@fetch_status = 0
  begin
-- obtain object type if it is a stored procedure or view
   if @xtype = 'P'
      begin
        set @sqlstring = 'drop procedure ' + @name
        exec sp_executesql @sqlstring
        set @sqlstring = ' '
      end
-- obtain object type if it is a view or stored procedure
   if @xtype = 'V'
      begin
         set @sqlstring = 'drop view ' + @name
         exec sp_executesql @sqlstring
         set @sqlstring = ' '
      end

    fetch next from AllSPViews_cursor into @name, @xtype
  end

close AllSPViews_cursor
deallocate AllSPViews_cursor

Always test above script within your test or sample database and be satisfied with results to check,
do not directly attempt on a live database that I will not give you any warranty or guarantee on
above task. Do not forget to have a