Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2011-12-15 : 10:56:50
|
| Hi,I want to empty the database (only data). Is there any script/SP available to that.SQL Server 2005 version |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-12-15 : 11:04:21
|
| Or you could:Make a new dbAnd script out all the objects from the old onethere is a wizard for that.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2011-12-15 : 11:27:37
|
| 106 tables. we do have RI in database.I want to keep Parent and Child relationships, I want to delete only data |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-15 : 11:37:54
|
| you've generate one by iterating through table list (use INFORMATION_SCHEMA.TABLES view for that) and build TRUNCATE TABLE query based on it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2011-12-15 : 11:54:40
|
| Task completed.I used below script-- disable all constraintsEXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"-- delete data in all tablesEXEC sp_MSForEachTable "DELETE FROM ?"-- enable all constraintsexec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Empty SQL Server 2005 database