Appframe Knowledge Base


1 hits
1

How do you truncate all tables in a database using TSQL?

/*What is the best way to remove all the data from all the tables using TSQL? Are there system stored procedures, views, etc. that can be used? I do not want to manually create and maintain truncate table statements for each table - I would prefer it to be dynamic.

http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql#156813
*/

/*
It would make sense to add filters to i.e. just clean up tables starting with atbl_ and/or those that contains the Domain field. But this can easily be added.
*/

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
exec sp_MSforeachtable 'DELETE FROM ?'  
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 
exec sp_MSforeachtable 'IF NOT EXISTS (SELECT *
    FROM SYS.IDENTITY_COLUMNS
    JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE SYS.TABLES.Object_ID = OBJECT_ID(''?'') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL)
    AND OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1
    DBCC CHECKIDENT (''?'', RESEED, 0) WITH NO_INFOMSGS'

sql · Perma link post comment Posted by: Trygve Haaland (08-nov-2012)