Drop All Objects

Here is a little script I find useful. It drops all tables, views and stored procedures in a database.

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

/* Drop all non-system stored procs */
declare @name varchar(128)
declare @SQL varchar(254)

SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'P' And category = 0 order by name)
while @name is not null
begin

select @SQL = 'drop procedure [dbo].[' + rtrim(@name) +']'
exec (@SQL)

print 'Dropped :' + @name

SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'P' And category = 0 and name > @name order by name)
end
go

/* Drop all views */
declare @name varchar(128)
declare @SQL varchar(254)
SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'V' And category = 0 order by name)
while @name is not null
begin

select @SQL = 'drop view [dbo].[' + rtrim(@name) +']'
exec (@SQL)

print 'Dropped :' + @name

SELECT @name = (select top 1 name FROM sysobjects WHERE type = 'V' And category = 0 and name > @name order by name)
end

go

Please, go ahead to improve it. I'm sure it is far from perfect.

• Share This Post:
• 
• 
•