delete all rows from all tables in a SQL Server database
and what about triggers ?????????
2009-12-14 09:29:08
for triggers refer http://www.sqlservercentral.com/Forums/Topic285605-5-1.aspx#bm822384
Pandiya Chendur
2009-12-14 09:36:55
i need a sp to do that....please provide a full sp
2009-12-14 09:46:25
+5
A:
Note that TRUNCATE won't work if you have any referential integrity set.
In that case, this will work:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Coder 42
2009-12-14 09:26:21
Actually, that's only for DDL triggers.In which case:EXECP sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
Coder 42
2009-12-14 09:34:19
A:
You could delete all the rows from all tables using an approach like Rubens suggested, or you could just drop and recreate all the tables. Always a good idea to have the full db creation scripts anyway so that may be the easiest/quickest method.
AdaTheDev
2009-12-14 09:26:23
seems OP is concerned about referential integrity and triggers; this case, your got best solution. I'm dropping my answer =)
Rubens Farias
2009-12-14 09:31:44