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
