emptying tables
-
Hi there.
Is there a way in which I can delete all the data in all the tables in a given database. I just want to delete the data in these tables, not the actual tables or columns.
how would this be done via tsql and/or enterprise manager.
Many thanks
-
RUN only in developpmenet area :
EXEC sp_MSForEachTable 'DELETE FROM ?'
-
Or use Truncate instead of delete.
-
I'm assuming that there is at least 1 foreign key in the whole DB. So truncate was out of the question
. -
EXEC sp_MSForEachTable 'DELETE FROM ?'
what stored procedure is this?
-
System procedure in the master database. It looks through all tables in the current database and executes the statement passed in paramter. The question mark is used as a placeholder for the fully qualified table name.
You can run this to see how it works :
EXEC sp_MSForEachTable 'Select ''?'''
-
If there are FK's the order of tables to delete data from is really important.
_____________
Code for TallyGenerator -
Ya that was the lazy solution. Ultimatelly you could run it untill no row are affected, meaning the db should be empty (excluding circular reference of course).
But I don't have time to write a script to find the delete order. Anyone got one handy?
-
Right-click on the database in EM. Choose All Tasks - Generate SQL Script.
Include all objects. Run. Save it. Run to create new empty database.
Runs faster than delete from a single table.
_____________
Code for TallyGenerator -
Good point... that's why I want to move to a bigger company ( meaning 100 GB db ). You don't learn that stuff on a 300mb db
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply