Truncate database

[mossbs]

Hey guys,

is there a way that you can truncate a whole database to leave all table structures and fk relationships just delete all the data from within tables... without having to do a table at a time?

Any help much appreciated.

Cheers,

Dan.

 

[markros]

Try using SP_MsForEachTable

http://www.google.com/url?sa=U&star...8IzfBg&usg=AFQjCNE26nQ4J4tbVcLYhiewicJoC23ArA

 

[gmmastros]

thread183-1496943

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Just wanted to fix the link I posted

http://www.databasejournal.com/feat...edures-spMSforeachtable-and-spMSforeachdb.htm

Thanks, George.

 

[SQLSister]

Personally I would script the whole data base and drop the old one and recreate it from the script. The problem with runnning theough the tables ina cursor comes with the foreign keys. You can't truncate a table that has foreign Keys. You could also write a script to drop all the foreign keys then runthough all the tables to truncate, then recreate all the foreign keys,

"NOTHING is more important in a database than integrity." ESquared

 

[mossbs]

cheers for the replies guys... pretty helpful.

i'm trying to do it using following code...

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'TRUNCATE table ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

but as yet to no avail

keeps returning following error......

Warning: The table 'AdvancedLookupSearchField' has been created but its maximum row size (8574) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'AdvancedLookupSearchField' has been created but its maximum row size (8574) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'ACOCMP1.LEVY' because it is being referenced by a FOREIGN KEY constraint.


any ideas?.... i'm not the best at this kinda stuff!

cheers all.

dan.

 

[dodge20]

When I do this I do it exactly as SQL Sister suggested.

Dodge20