How can i drop all tables (and ref. integ) in a database in one action
Nidyahia Mohamed
Hello database masters,
I am looking for a quick solution to drop all tables in a database.
In our team we develope many databases for our customers,
In the first steps of developing, we change enought often the design
(we work with Power Designer - Sybase) and we would like
to drop all tables and recreate them with modifications.
The problem is : we have a lot of referencial integrities and tables.
Then we cannot drop all tables because the referential integrities
prevent this action.
It's too difficult to delete table by table according to ref. integr.
My real question is : how can i delete all ref. integr of a database in
one action ?
If there is a solution then after this action i can drop all tables in
one action !
Thank you for your hand.
--
Nidyahia M. - DBA / Internet Developer - m...@allmansland.com
Allmansland - a De Schutter company
Priemstraat 53 Rue du Poincon - B-1000 Brussels 1 Belgium
Tel. +32 2 5481700 - Fax +32 2 5481717 - http://www.allmansland.com/
Steve Agan
What about drop database?
steve
In article <3534F9B6...@allmansland.com>, m...@allmansland.com says...
Gary Hubbard
This way won't delete them all in one fell swoop, but it's pretty quick.
Use the following SQL to quickly generate a drop table script:
select 'drop table', name from sysobjects where type = 'U'
Once the 'drop table' commands are generated, simply execute them. Only the
tables that you are not violating RI on will be dropped, but if you make a
few passes (ignoring table not found warnings) with the same commands,
eventually all of the tables will removed.
Another option is if you have a GUI tool available like Desktop DBA. With
this kind of interface, you can highlight a set of tables (which can be all
of them) and request that they be dropped.
I hope this is helpful.
Gary Hubbard
Sr. Database Administrator
Pioneer Hi-Bred Int'l, Inc.
Knut Erik Terjesen
If you run this script it will generate a new script with the alter and drop
statements you need
/* to drop all tables in a database with declarativ RI
** this script generates a alter / drop script
*/
/* create alter table script for declarative RI */
select
"alter table " + sot.name + " drop constraint " + soc.name + char(13) +
char(10) + "go"
from
sysconstraints sc, sysobjects sot, sysobjects soc
where
sc.tableid = sot.id and
sc.constrid = soc.id and
sc.status = 64
go
/* then drop all tables */
select 'drop table ' + name + char(13) + char(10) + "go"
from sysobjects
where type = 'U'
Knut Erik Terjesen
Nidyahia Mohamed wrote in message <3534F9B6...@allmansland.com>...
Alex Dumas
>> I am looking for a quick solution to drop all tables in a database.
The quickest solution:
dump model database to disk.
load the dump of the model on the databases.
THEN you can drop the databases.
Kerem
kk...@sybase.com
Pablo Sanchez
>>>>> "Alex" == Alex Dumas <alex...@hotmail.com> writes:
Alex>
Alex> dump model database to disk.
Alex> load the dump of the model on the databases.
Alex> THEN you can drop the databases.
Unfortunately the above won't work. If model is 2MB and
user db is 100M... no can do.
select "drop table " + name + char(13) + "go" from
my_db..sysobjects where type='U'
send the above to a file and redirect it to "isql"
--
Pablo Sanchez | Ph # (650) 933.3812 Fax # (650) 933.2821
pa...@sgi.com | Pg # (800) 930.5635 -or- pab...@pager.sgi.com
-------------------------------------------------------------------------------
I am accountable for my actions. http://reality.sgi.com/pablo
Jeremy Posner
Tools like ERWin and Powerdesigner produce scripts that will perform the
drops and re-creates in the right order.
just a thought
Pablo Sanchez
>>>>> "Anthony" == Anthony Mandic <no_s...@agd.nsw.gov.au> writes:
Anthony>
>> Unfortunately the above won't work. If model is 2MB and
>> user db is 100M... no can do.
Anthony> Incorrectamundo!
Oh that's right! I was thinking... backwards... :-)
Nidyahia Mohamed
I now that, i use PowerDesigner but he can't drop tables with RI
automatically !
We have searched for options to do that but nothing.
Read you later...
Jeremy Posner wrote:
--