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 complete database backup that has been tested further with a restore on to another server, taking or keeping backup is not sufficient enough!!!