May 12, 2010 at 10:38 am
I need to purge all the data from all the user tables in a database. I know I've seen some articles on this site on how to do this quickly and easily, but I can't find them. As I recall, the script would traverse all the relationships and "truncate" data in the correct order.
Can anyone point me in the right direction?
.
May 12, 2010 at 11:21 am
I don't have my scripts right now but I usually use two. The first is a truncate script that simply executes a: TRUNCATE TABLE tablename
against all tables, that takes care of the tables than can be truncated. Then I use one that executes a: DELETE FROM tablename
on any table that has a rowcount > 0, it doesn't care about relationships. I usually run it 3-5 times. When it is done there is no data in any user table. It is just easier to not worry about the relationships. This method doesn't care, if it can't delete the delete fails the first couple of times, but once the data holding up the delete is gone it works.
If you have LARGE tables, then the BEST option is to remove the foreign key relationships and just truncate the tables.
I have seen some scripts that drop all the relationships, truncate all the tables, and then try to re-add the relationships.
CEWII
May 12, 2010 at 11:49 am
here's what i use;
due to a naming convention, i know i can skip tables that begin with certain characters; you might have similar logic which designates which tables are lookup tables vs user tables.
this snippet creates all the command to delete/truncate in foreign key hierarchy order, so you only need to run this once to clear your user tables;
you'll undoubtedly need to change the contents of the "Skipme" table, but that's trivial:
nocount on
CREATE TABLE #Skipme(TableName varchar(255))
INSERT INTO #Skipme
SELECT 'tbCity' UNION ALL
SELECT 'tbState' UNION ALL
SELECT 'tbCounty' UNION ALL
SELECT 'OtherLookupTables'
INSERT INTO #Skipme
SELECT name from sys.objects
where LEFT(name,2) = 'LU'
OR LEFT(name,2) = 'TB'
declare @level tinyint
set @level = 0
create table #tables (
id int not null primary key clustered,
TableName varchar(255) not null,
Level tinyint not null)
insert into #tables (id, TableName, Level)
select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0
from sysobjects where xtype = 'U' and status > 0
while @@rowcount > 0 begin
set @level = @level + 1
update rt set Level = @level
from #tables rt
inner join sysreferences fk on fk.rkeyid = rt.id
inner join #tables ft on ft.id = fk.fkeyid
where ft.Level = @level - 1
end
print 'USE ' + DB_NAME() + '
'
select 'TRUNCATE TABLE ' + TableName
from #tables
where level = 0
And TableName Not In (SELECT TableName from #Skipme)
select 'DELETE ' + TableName
from #tables
where level > 0
And TableName Not In (SELECT TableName from #Skipme)
order by level
drop table #tables
Lowell
May 12, 2010 at 11:55 am
Perfect. Thank you.
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply