Hi kids...
Here's a hot action script for truncating all the tables in your SQL 2005 db for you. Please post your comments, changes, etc. and I'll update this throughout:
select distinct
t.table_schema + '.' + t.table_name as [table]
, case when tc.constraint_type = 'FOREIGN KEY' then 1 else 2 end as [order]
into #master_tbl
from information_schema.tables t
inner join information_schema.table_constraints tc on
tc.table_schema = t.table_schema and
tc.table_name = t.table_name
where t.table_type = 'BASE TABLE'
and tc.constraint_type like '% KEY'
order by 2
alter table #master_tbl
add idx bigint identity(1,1)
select *
into #k
from #master_tbl
select *
into #rk
from #k
while ((select count(*) from #k) > 0)
begin
declare @table varchar(1024)
declare @idx bigint
select top 1
@table = [table]
, @idx = idx
from #k
exec ('alter table ' + @table + ' nocheck constraint all')
delete from #k where idx = @idx
end
drop table #k
select *
into #t
from #master_tbl
while ((select count(*) from #t) > 0)
begin
declare @tidx bigint
declare @ttable varchar(1024)
select top 1
@tidx = idx
, @ttable = [table]
from #t
order by [order]
begin try
exec('truncate table ' + @ttable)
print @ttable + ' purged'
end try
begin catch
print @ttable + ' could not be purged.'
end catch
delete from #t where idx = @tidx
end
drop table #t
while ((select count(*) from #rk) > 0)
begin
declare @ktable varchar(1024)
declare @kidx bigint
select top 1
@ktable = [table]
, @kidx = idx
from #rk
exec ('alter table ' + @ktable + ' with check check constraint all')
delete from #rk where idx = @kidx
end
drop table #rk
drop table #master_tbl
Cheers...
