October 16, 2006 at 4:55 pm
Hello .
I'm looking for a program/script that generates a clear script for the specified DB.
I need it to clear databases with test data.
( it should just generate DELETE statements in the right order )
I guess it's not that comlicated ( it's only an algorithm to
find the right DELETE order based on FK's )
I couldn't find one on the net , but I'm pretty sure such a program
exists .
Anyone knows ?
Thanks .
October 16, 2006 at 6:31 pm
Well .. my mistake was that I've searched all over the net except the forums here.
Using this thread :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=298668
I've found a solution .
In my case I needed some tables to be non-touched ,
so here how I've done that :
exec sp_msforeachtable
@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL",
@command2 = "DELETE ?",
@command3 = "ALTER TABLE ? CHECK CONSTRAINT ALL",
@whereand = ' and name not like ''%LUT%'' and name not in
(''USER'',''USER_GROUP'',''SECTION_GROUP'')'
Though it satisfies me , still .. it's not exactly what I looked for .
( a program that generates the DELETE statements in the right order )
So my question is still open .. just for curiosity - if anyone knows such a program.
Thanks again.
October 17, 2006 at 8:13 am
here's a simple example: assuming ('TBSTATE','TBSTATE','TBCOUNTY') is the lookup tables that should not be deleted
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('TBSTATE','TBSTATE','TBCOUNTY')
select 'DELETE ' + TableName from #tables where level > 0 AND TableName not in('TBSTATE','TBSTATE','TBCOUNTY')
order by level
drop table #tables
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply