Clear All Data Tables In Database

[DomDom3]

Hi there,

I need to make a blank version of a database. What would be th eeasiest way of clearing the data from all the tables?

Thanks

 

[ptheriault]

You could just script out the database
Right click on the db and select all tasks -> Generate script.
Or if you already have a database you need to claer run this.

sp_MSforeachtable @command1 = "truncate table ?"

you may have problems with contraints if you have any. You will have to disable them first. let me know if you do I can provide you with a script that will disable all constraints.

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

This script will run faster than just the plain delete.

Set nocount on

Exec sp_MSForEachTable 'Alter Table ? NoCheck Constraint All'

Exec sp_MSForEachTable
'
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1
Begin
-- Just to know what all table used delete syntax.
Print ''Delete from '' + ''?''
Delete From ?
End
Else
Begin
-- Just to know what all table used Truncate syntax.
Print ''Truncate Table '' + ''?''
Truncate Table ?
End
'

Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'

- Paul
- Database performance looks fine, it must be the Network!