1 개요

2 스크립트

관계를 맺고 개발할 때에 truncate를 하기 어렵다는 불편함이 있다. 그래서 스크립트를 만들었다. 다음과 같은 모델이 있다.

만약 테이블 c를 truncate 하고 싶다면

를 실행하면 되고, 테이블 a를 truncate 하고 싶다면
를 실행하면 된다. 그럼 결과가 다음과 같이 나올 것이다.

alter table dbo.b drop constraint FK_b_a
alter table dbo.c drop constraint FK_c_a
alter table dbo.d drop constraint FK_d_c
alter table dbo.e drop constraint FK_e_d
alter table dbo.fd drop constraint FK_fd_d
alter table dbo.g drop constraint FK_g_fd
truncate table dbo.a
truncate table dbo.b
truncate table dbo.c
truncate table dbo.d
truncate table dbo.e
truncate table dbo.fd
truncate table dbo.g
alter table dbo.b with nocheck add constraint FK_b_a foreign key(akey) references dbo.a(akey)
alter table dbo.c with nocheck add constraint FK_c_a foreign key(akey) references dbo.a(akey)
alter table dbo.d with nocheck add constraint FK_d_c foreign key(ckey_r,akey_r) references dbo.c(ckey,akey)
alter table dbo.e with nocheck add constraint FK_e_d foreign key(dkey) references dbo.d(dkey)
alter table dbo.fd with nocheck add constraint FK_fd_d foreign key(dkey) references dbo.d(dkey)
alter table dbo.g with nocheck add constraint FK_g_fd foreign key(dkey,fkey) references dbo.fd(dkey,fkey)

대충 만들어 봤다.

if schema_id('mng') is null
	create schema mng
go

create proc mng.relationship_drop_add
	@tname varchar(500)
as
--exec mng.relationship_drop_add 'dbo.a'

set statistics io off
set nocount on 
/*
declare 
	@tname varchar(255)
set @tname = 'cust.account'
*/

declare 
	@lv int
,	@db varchar(255)
,	@sql varchar(8000)

set @db = db_name()
set @lv = 1


if object_id('tempdb.dbo.#temp') is not null
	drop table #temp
	
create table #temp(
	lv int
,	parent_table varchar(500)
,	child_table varchar(500)
,	const_name varchar(500)
,	child_col varchar(255)
,	parent_col varchar(255)
)

if object_id('tempdb.dbo.#const') is not null
	drop table #const
	
select
	d.sch_nm + '.' + object_name(rkeyid) parent_table
,	e.sch_nm + '.' + object_name(fkeyid) child_table
,	object_name(constid) const_name
,	b.name child_col
,	c.name parent_col
into #const
from sys.sysforeignkeys a
	inner join sys.syscolumns b
		on a.fkeyid = b.id 
		and a.fkey = b.colid
	inner join sys.syscolumns c
		on a.rkeyid = c.id 
		and a.rkey = c.colid
	inner join (
		select
			a.id obj_id
		--,	object_name(a.id)
		,	b.name sch_nm
		from sys.sysobjects a
			inner join sys.schemas b
				on a.uid = b.schema_id
	) d
		on a.rkeyid = d.obj_id
	inner join (
		select
			a.id obj_id
		,	b.name sch_nm
		from sys.sysobjects a
			inner join sys.schemas b
				on a.uid = b.schema_id
	) e
		on a.fkeyid = e.obj_id		
order by parent_table,child_table

insert #temp
select
	@lv
,	parent_table
,	child_table
,	const_name
,	child_col
,	parent_col
from #const
where parent_table = @tname

while(1=1)
begin
	insert #temp
	select
		@lv + 1
	,	parent_table
	,	child_table
	,	const_name
	,	child_col
	,	parent_col
	from #const	
	where parent_table in (select child_table from #temp where lv = @lv)
	
	if @@rowcount = 0 break;
	set @lv = @lv + 1
end

if object_id('tempdb.dbo.#rs') is not null
	drop table #rs
	
;with rs
as
(
	select
		a.lv
	,	a.parent_table
	,	a.child_table
	,	a.const_name
	,	stuff((select ',' + child_col as 'text()'
		from #temp
		where lv = a.lv
		and parent_table = a.parent_table
		and child_table = a.child_table
		and const_name = a.const_name
		 for xml path('')),1,1,'') child_col
	,	stuff((select ',' + parent_col as 'text()'
		from #temp
		where lv = a.lv
		and parent_table = a.parent_table
		and child_table = a.child_table
		and const_name = a.const_name
		 for xml path('')),1,1,'') parent_col	 
	from (
		select distinct
			lv
		,	parent_table
		,	child_table
		,	const_name
		from #temp) a
)
select 
	lv
,	'alter table ' + child_table + ' drop constraint ' + const_name drop_const
,	'alter table ' + child_table + ' with nocheck add constraint ' + const_name + 
	' foreign key(' + child_col + ') references ' + 
	parent_table + '(' + parent_col + ')' add_const
into #rs
from rs


--관계를 끊자
declare 
	@drop varchar(8000)
,	@add varchar(8000)
,	@trunc varchar(8000)

declare cur1 cursor for
	select
		drop_const
	from #rs

open cur1;
fetch next from cur1 into @drop;
while @@FETCH_STATUS not in (-1, -2)
begin
	--exec(@drop);
	print @drop;
	fetch next from cur1 into @drop;
end

close cur1;
deallocate cur1;

--truncate 하자
declare cur2 cursor for
	select 'truncate table ' + parent_table
	from (
		select parent_table from #temp
		union
		select child_table from #temp
	) t

open cur2;
fetch next from cur2 into @trunc;
while @@FETCH_STATUS not in (-1, -2)
begin
	--exec(@trunc);
	print @trunc;
	fetch next from cur2 into  @trunc;
end

close cur2;
deallocate cur2;

--관계를 다시 맺어주자
--declare 
--	@drop varchar(8000)
--,	@add varchar(8000)
--,	@trunc varchar(8000)

declare cur3 cursor for
	select
		add_const
	from #rs

open cur3;
fetch next from cur3 into @add;
while @@FETCH_STATUS not in (-1, -2)
begin
	--exec(@add);
	print @add
	fetch next from cur3 into @add;
end

close cur3;
deallocate cur3;

select
	a.lv
,	a.parent_table
,	a.child_table
,	a.const_name
,	stuff((select ',' + parent_col as 'text()'
	from #temp
	where lv = a.lv
	and parent_table = a.parent_table
	and child_table = a.child_table
	and const_name = a.const_name
	 for xml path('')),1,1,'') parent_col	
,	stuff((select ',' + child_col as 'text()'
	from #temp
	where lv = a.lv
	and parent_table = a.parent_table
	and child_table = a.child_table
	and const_name = a.const_name
	 for xml path('')),1,1,'') child_col
from (
	select distinct
		lv
	,	parent_table
	,	child_table
	,	const_name
	from #temp) a
order by 1,2,3