Skip to main content

Posts

Showing posts from January, 2013

Clear all the rows in a table resetting the identity specification back to zero and without affecting the foreign keys?

You can take the following steps: -- disable all foreign key constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- delete data in all tables EXEC sp_MSForEachTable "DELETE FROM ?" -- enable all constraints exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" More on disabling constraints and triggers here if some of the tables have identity columns we may want to reseed them EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)" Note that the behaviour of RESEED differs between brand new table, and one which had had some date inserted previously from BOL