It there an easy way to 'clean out' a SQL Server database? I need to get a
database of an unknown state back to a known state of 'empty'. Is there an
easy way to drop ALL structures; TABLES, VIEWS, SPROCS, FUNCTIONS, INDEXES,
TRIGGERS, CONSTRAINTS, etc. (Did I miss anything?)

(I don't want to rely on Dropping the Database and recreating it entirely as
a new database, since this requires a priviledge escalation as compared to
the priviledges required for working on structures inside of the database.)

Thanks for your help!

- Joseph Geretz -

You could easily write a proc with a little cursor to do this using

2005:
select name, type
from sys.objects
2000
select name, xtype
from sysobjects
Then just use the type of proc to determine what drop statement to use. I
think this will do it, even considering constraints (sometimes you have to
drop the constraint before the columns,) I think if you limit this to:

sys.objects.type in ('P', 'FN', 'U', 'IF', 'TF', 'V') --pretty much the same
in 2000
--procedure, scalar function, user table, inline function, table value
function
You can do it. You would just have to run the cursor multiple times to
eliminate dependencies, or you could add foreign keys if you want to using
the information_schema,table_constraints for that.
I might also suggest you could just write a sql agent job that could be
executed to clean out the database pretty easily by killing any users,
dropping the database and recreating it, if you really want to just start
over.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote

Here's a link to a script to drop all user objects using the basic technique
Louis suggested. You can tweak for SQL 2005, although the need to do this
might be mitigated if you can do DROP/CREATE DATABASE instead.

http://tinyurl.com/9t9m6

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote

Joseph

you can back up an empty database, then restore the backup against the
database you wish to clean up

HIH

Thanks Dan!

Your script works perfectly for us.

- Joe Geretz -

"Dan Guzman" <guzmanda (AT) nospam-online (DOT) sbcglobal.net> wrote

Nice

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

"Dan Guzman" <guzmanda (AT) nospam-online (DOT) sbcglobal.net> wrote