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 -
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
|||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...
>
sqlsql

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...
>

City, State expression problem!

I have a report which lists the city and state. The problem is that
the city and state are in one field and were in Upper case. I used the
strconv but I don't know how to make the state portion Upper instead or
Proper.
Right now the output looks like this: Birminham, Al
Any help is appreciated.Try the following:
select replace(ColName,right(ColName,1), Upper(right(ColName,1)))
Good Luck
swtjen01 wrote:
> I have a report which lists the city and state. The problem is that
> the city and state are in one field and were in Upper case. I used the
> strconv but I don't know how to make the state portion Upper instead or
> Proper.
> Right now the output looks like this: Birminham, Al
> Any help is appreciated.

Checking the state of a variable as first step of package?

Am I correct in thinking that I need to place a dummy script component which does nothing in order to route logic depending on the value of a variable?

ie. I want to output to a file if a Parent variable is a certain value, or i want to load a table if its a different value.

Unfortunately, yes.

Check the syntax

I'm getting the following error messages:
Incorrect syntax near the keyword 'in'.
Server: Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'group'.

/* create temp tables */
select distinct d_vst_id as 'DRW_ID'
,d_vst_instid as 'DRW_INSTID'
into temp_tb1
from dnr_vst_db_rec
where d_vst_instid = ''
and d_vst_dontyp = 'WB'
and d_vst_status = 'DN'
and d_vst_date between 20020301 and 20030228
order by d_vst_id

Select distinct
n_per_id as 'ID1'
,n_per_gender as 'GENDER'
,n_per_birth as 'BIRTH1'
,d_bty_abo + d_bty_rhesus as 'ABO1'
,n_adr_city as 'CITY1'
,n_adr_zip as 'ZIP1'
into temp_tb3
from temp_tb1 right outer join nat_per_db_rec
on DRW_ID = n_per_id
right outer join dnr_bty_db_rec
on DRW_ID = d_bty_id
right outer join nat_adr_db_rec
on DRW_ID = n_adr_id
where DRW_INSTID = ''
order by n_per_id

select distinct getdate()
,d_aaa_insthdg
, case (d_vst_btcdte - n_per_birth) / 10000
when in (14,15,16) the '14-16'
when in (17,18,19,20) then '17-20'
when in (21, 22,23,24,25) then '21-25'
when in (26,27,28,29,30) then '26-30'
when in (31,32,33,34,35) then '31-35'
when in (36,37,38,39,40) then '36-40'
when in (41,42,43,44,45) then '41-45'
when in (46,47,48,49,50) then '46-50'
when in (51,52,53,54,55) then '51-55'
when in (56,57,58,59,60) then '56-60'
when in (61,62,63,64,65) then '61-65'
when in (66,67,68,69,70) then '66-70'*/
else
71+
end as 'AGE'
,sum(case a.d_vst_dontyp when '1' then 1 else 0 end ) as 'DRAW1'
,sum(case a.d_vst_dontyp when 'xx' then 1 else 1 end ) as 'TOTAL'
from dnr_aaa_db_rec, dnr_dud_db_rec, temp_tb3, dnr_vst_db_rec a
where a.d_vst_instid = ''
and a.d_vst_instid = d_aaa_instid
and a.d_vst_id = ID1
and a.d_vst_instid = n_per_instid
and a.d_vst_id = n_per_id
and n_per_gender = 'M'
and a.d_vst_btcdte between 20020301 and 20030228
and a.d_vst_btcdte = (Select max(b.d_vst_btcdte)
from dnr_vst_db_rec b
where b.d_vst_instid = ''
and b.d_vst_status = 'DN'
and b.d_vst_dontyp = 'WB'
and b.d_vst_id = a.d_vst_id
and b.d_vst_btcdte between 20020301 and 20030228)

group by
d_aaa_insthdg
,case (d_vst_btcdte - n_per_birth) / 10000
when in (14,15,16) the '14-16'
when in (17,18,19,20) then '17-20'
when in (21, 22,23,24,25) then '21-25'
when in (26,27,28,29,30) then '26-30'
when in (31,32,33,34,35) then '31-35'
when in (36,37,38,39,40) then '36-40'
when in (41,42,43,44,45) then '41-45'
when in (46,47,48,49,50) then '46-50'
when in (51,52,53,54,55) then '51-55'
when in (56,57,58,59,60) then '56-60'
when in (61,62,63,64,65) then '61-65'
when in (66,67,68,69,70) then '66-70'*/
else
71+
end asI think you have 2 problems. First, move (d_vst_btcdte - n_per_birth) / 10000 to be between "when" and "in". Second, in both case statements you have 71+ followed by "*/". You need to surround 71+ with apostrophes and remove */.