SQL to empty records from all tables in a given database

[s72dba]

Is their a faily quick way to write a TSQL script or another way to purge all records from all tables under one particular Database? The user has asked for a clean set of empty tables to be established before a conversion process
occurs.

 

[mrdenny]

Like this.

exec sp_MSforeachtable 'truncate table ?'

You may need to change that to delete statements if you have forign keys in place. Also you may need to run it more than once as you can't control what order tables are processed in.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog