Geeks With Blogs
Clara's Notes on Technology Notes to me and the world

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.

 

Technorati tags:
Posted on Sunday, August 26, 2007 7:14 PM | Back to top


Comments on this post: Drop All Objects

# re: Drop All Objects
Requesting Gravatar...
Thanks for the script... It helped a lot.
Left by rye on Nov 24, 2007 7:12 PM

Your comment:
 (will show your gravatar)


Copyright © Clara Oscura | Powered by: GeeksWithBlogs.net