May 16, 2008 at 9:42 am
I am a Java developer, not a DBA. I am trying to delete all data from almost all of the tables in a particular database. We have used osql and an input script in the past. The script just said 'DELETE FROM ...' for each table and was ordered appropriately to avoid constraint conflicts. This has become difficult to maintain because we frequently add/delete tables. I would like something more dynamic. I want to find all of the tables in the database and delete the contents of each EXCEPT for a certain set. In addition, I need the delete to not error on the constraints.
I found this link that has a script to do what I want but there is no way to exclude a set of tables:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PurgeAllTables
Not being a procedure person, I don't know how to modify this to make the exclusions work.
I was successful in writing a small script that found every table and created the 'DELETE FROM' script for me. But I ran into the constraint problem. I would have to put each 'DELETE FROM' statement in a particular order to avoid the constraints.
Any ideas on how to make this work? Thanks in advance.
May 16, 2008 at 9:57 am
Why not just keep looping through the tables. Eventually the constraints will go away.
Your might want to use truncate instead of delete.
May 16, 2008 at 10:05 am
You mean, let it delete what it can, then go back again? That makes sense. I just have to figure out how to get it to loop (we have very novice users that won't remember to keep running it). Thanks for the suggestion.
May 16, 2008 at 11:41 am
declare @Rows int
select @rows = 1
while @rows > 0
begin
select @rows = 0
delete from dbo.Table1
select @rows = @rows + @@rowcount
delete from dbo.Table2
select @rows = @rows + @@rowcount
delete from dbo.Table3
select @rows = @rows + @@rowcount
end
Something like that will loop through until there are no row it can delete.
Truncate instead of delete will be faster, and will take up less log space, but is more complicated to monitor, since it doesn't populate @@rowcount.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 16, 2008 at 11:51 am
Try modifying this query to exclude specific tables:
SELECT QUOTENAME(TABLE_SCHEMA) AS schemaname, QUOTENAME(TABLE_NAME) AS name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Quick and dirty would be like this, Jeff will come along in a minute and fix my code:
SELECT QUOTENAME(TABLE_SCHEMA) AS schemaname, QUOTENAME(TABLE_NAME) AS name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN ('sometable', 'someothertable')
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply