Clean out (Reinitialize) SQL Server Database?

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 -What are you actually trying to accomplish here? If you drop all of the
database objects, what are you trying to preserve for future use?
========================================
==========
People who want to share their religious views with you almost never
want you to share yours with them. -Dave Barry, author and columnist
(1947- )
*** Sent via Developersdex http://www.examnotes.net ***|||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@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> 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 -
>|||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@.nospam.com> wrote in message
news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> 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 -
>|||Joseph
you can back up an empty database, then restore the backup against the
database you wish to clean up
HIH|||> What are you actually trying to accomplish here? If you drop all of the
> database objects, what are you trying to preserve for future use?
I can't speak for the OP but I've done this in SQL 2000 as an alternative to
DROP/CREATE DATABASE. This is significantly faster when the database is
large. However, in SQL 2005, extent formatting is deferred so that reason
doesn't apply. In SQL 2005, DROP/CREATE is better/faster as long as the
user has CREATE DATABASE permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen Hendricks" <shendricks@.datalabs.com> wrote in message
news:uIUs5efHGHA.916@.TK2MSFTNGP10.phx.gbl...
> What are you actually trying to accomplish here? If you drop all of the
> database objects, what are you trying to preserve for future use?
> ========================================
==========
> People who want to share their religious views with you almost never
> want you to share yours with them. -Dave Barry, author and columnist
> (1947- )
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Dan!
Your script works perfectly for us.
- Joe Geretz -
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> 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@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||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@.nospam-online.sbcglobal.net> wrote in message
news:uhxmpBrHGHA.1088@.tk2msftngp13.phx.gbl...
> 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@.nospam.com> wrote in message
> news:ezib0ueHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>