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

open AllSPViews_cursor

fetch next from AllSPViews_cursor into @name, @xtype

while @@fetch_status = 0

begin

-- obtain object type if it is a stored procedure or view

-- obtain object type if it is a view or stored procedure

if @xtype = 'PK'

begin

set @sqlstring = 'drop Constraint ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'FK'

begin

set @sqlstring = 'drop Constraint ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'V'

begin

set @sqlstring = 'drop view ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if (@xtype = 'FN' or @xtype='TF')

begin

set @sqlstring = 'drop Function ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'U'

begin

set @sqlstring = 'drop table ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

if @xtype = 'P'

begin

set @sqlstring = 'drop procedure ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

fetch next from AllSPViews_cursor into @name, @xtype

end

close AllSPViews_cursor

deallocate AllSPViews_cursor