Life as an Evangelist

Drop All Tables, Stored Procedures and Views in SQL Server

Recently, I had a problem with my database host. My database is screwed up but I cannot recreate the database since I will be charged again for hosting fees.

Fortunately for me, Romeo Ubaldo was available in MSN Messenger that time and my problem was solved.

He thought me of these two things:

How to Drop All Tables in a Database in SQL Server:

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

and

How to Drop All Stored Procedures and Views in a Database in SQL Server (credit also goes to this website):

create procedure DropSPViews
as

-- variable to object name
declare @name  varchar(100)
-- variable to hold object type
declare @xtype char(1)
-- variable to hold sql string
declare @sqlstring nvarchar(1000)

declare SPViews_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 SPViews_cursor

fetch next from SPViews_cursor into @name, @xtype

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

-- get next record
    fetch next from SPViews_cursor into @name, @xtype
  end

close SPViews_cursor
deallocate SPViews_cursor

Now, my problem is solved, and I can go and continue on what I've been doing.