Sunday, November 2, 2008

Clearing all tables via Truncate

Deleting data in all tables (providing constraints don't stop you)

Use with care!!!

Version 1 : Generate sql to do this
SELECT 'TRUNCATE TABLE [' +table_schema + '].[' + TABLE_NAME +']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE_TABLE'
ORDER BY 1

Version 2: Perform the truncates directly
EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?;'  


NB :You could use these in conjunction with Disable all Foreign Keys and Triggers or the techniques I employ in Empty database script

No comments: