Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fast delete ! 3

Status
Not open for further replies.

3615

Programmer
Jun 22, 2001
22
FR
I'm using MSDE

I want to delete all datas of a database(60 tables with relations, indexes, contraints...).

I can't use truncate table (Relations...),
and Delete * from Table1,2,3 ... take a long time.

Question ?

does it possible to create a SP

1 to get a script with all relations, indexes ...
2 to Remove all relations, contraints ...
3 Truncate all tables
4 and finally to restablishe all relations

There is a better way to does this work ?

Thanks
 
What I would do in this situation is the following:

1) Backup database.
2) From Enterprise manager highlights database and generate a full creation script on all objects.

3) Drop database.
4) Create database.
5) Run the script generated in step 2.

This should give you the desired results in the most efficient manner.

Rick.
 

You can disable contraints on all tables which will allow you to truncate the tables without dropping tables, keys or indexes.

Disable all constraints on a table.
ALTER TABLE tablename NOCHECK CONSTRAINT ALL

Enable all constraints on a table.
ALTER TABLE tablename CHECK CONSTRAINT ALL

You can quickly generate the disable, truncate and enable SQL code with the following script.

Use MyDB

set nocount on
Select 'ALTER TABLE ' + u.name + '.' + o.name + ' NOCHECK CONSTRAINT ALL'
From sysobjects o Inner Join sysusers u
On o.uid=u.uid
Where o.type='u'
And o.name<>'dtproperties'

Select 'TRUNCATE TABLE ' + u.name + '.' + o.name
From sysobjects o Inner Join sysusers u
On o.uid=u.uid
Where o.type='u'
And o.name<>'dtproperties'

Select 'ALTER TABLE ' + u.name + '.' + o.name + ' CHECK CONSTRAINT ALL'
From sysobjects o Inner Join sysusers u
On o.uid=u.uid
Where o.type='u'
And o.name<>'dtproperties'
set nocount off Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous
 
thank you tlbroadbent

that is exactly what i need ...
 
Sorry terry

ALTER TABLE &quot;MyTable&quot; NOCHECK CONSTRAINT ALL

doesn't allow to truncate table !

another Tips ?
 

Did you disable all constaints on all tables before trying to truncate a table? Do you get an error message? It would be very helpful to know the message you get. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous
 
Hi terry

here is my problem

-The tables
TABLEA
IDA int
Lib varchar(10)

TABLEB
IDB int
IDA int
FirstName varchar(20)
Adress varchar(50)

- first i do this
ALTER TABLE TABLEA NOCHECK CONSTRAINT ALL

- second i do this
alter TABLE TABLEB NOCHECK CONSTRAINT ALL

-third i try this
truncate table TABLEA

and i get this error (in french)
Serveur: Msg 4712, Niveau 16, État 1, Ligne 1
Impossible de tronquer la table 'TABLEA' parce qu'elle est présentement référencée par une contrainte de clé étrangère.

in english ....
unable to truncate TABLEA because there is actually a Foreign Key constraint

what's happen !
 

I just tested the script and found that disabling the constraints allows me to delete records but not Truncate the table. Funny that SQL should handle it that way.

I've found a way to quickly drop the contraints rather than disable them. There is an undocumented stored procedure, sp_MSdroparticleconstraints, that will drop all constraints on a table. Usage is as follows.

exec sp_MSdroparticleconstraints tableB, dbo

After executing this for all tables, you should be able to truncate the tables. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Now it's allright !

Before, i must generate a script to keep all existing contraints, then i use sp_MSdroparticleconstraints for all tables, i can delete tables, and after rebuild all contraints using the script...

Thanks Terry
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top