How to delete records in all tables in a single database
-
That depends on whether you have any foreign keys and/or triggers. If you have neither, then a simple sql generating sql statment will generate the script you need.
SELECT 'DELETE '+ name
FROM sysobjects
WHERE xtype = 'U'
AND name <> 'dtproperties'
If you have FK's you will have to perform the DELETEs in the proper order, unless you want to drop the constraints and disable the triggers, perform the DELETEs and then recreate the constraints and enable the triggers etc...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Just switch to the database in question and run the statement that I posted. It will not actually perform the DELETEs but it will generate the DELETE statements for each table in your database. Review the script that is created and then cut and paste it from the results pane back into the query window and run it. That's it, all data will be gone from all the user tables in the database.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
hello dcpeterson,
please ignore from here.......
_______________________________________________________________
thanks for the response. i am afraid that i ran the script that you gave me against the 'master' database
!!!!!!!!! but didn't you say that it only generates scripts for the actual delete ??? in the results pane there were 'deletename' for each of the user tables in the 'master' database. i DID NOT do anything with those!! please tell me that i DID NOT alter/change/corrupt the master table 
!!! this time i will try it out on the correct table
....which i did and in the results pane (which i suppose is the bottom pane in the query analyzer!) i got deleteTable1, deleteTable2, deleteTable3, deleteTable4. are these the delete scripts that you were referring to? coz i ran one of them but it didn't do the trick. i am assuming here that in your script there should be a space soon after delete...like select 'delete ' +name...right?
_____________________________________________________________
...................to here!
...and yes it worked that way!! now you see, i have some databases that has like 50 tables in it...so is there a way to do it another way instead of copying and pasting delete name 50 times! can u give your insight on this syntax which i found in another forum:
exec sp_msforeachtable 'truncate table ?'
or
exec sp_msforeachtable 'truncate table "?"'
which of these is right and is this accurate and how and where would i run/execute it?
sorry for this looooooooooooooong post. thanks for all ur help.
Ram
-
Unless you cut and pasted the DELETE statements into the query window and then ran them, you are fine.
P.S. make sure there is a space between the DELETE and the end tick (') i.e. 'DELETE ', not 'DELETE' otherwise you will get errors when you run them
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
exec sp_msforeachtable "truncate table ?"
would be the proper syntax. I didn't tell you to use this for two reasons.
First that procedure is undocumented and thus unsupported by MS.
Second, that statement will simply truncate all your tables, using my method, you get to do a "sanity check" before any data gets deleted. I would only use the sp_msforeachtable if you REALLY know what you are doing.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply