이미 구축된 사이트 리뉴얼을 할때 커다른 이슈사향중 하나는 DB이관 작업이다.
이 DB이관이 어려운 이유는 리뉴얼시 대부분 DB 의 테이블 구조, 항목등이 변경되기 때문이다.
아예 새로 DB를 구축하고 새로운 구조로 만든후 기존 데이타를 포팅한다면 포팅시에만 주의하면 된다.
근데 새로운 구조가 아닌 기존 DB구조를 그대로 유지한채 몇개의 필드, 몇개의 테이블이 삭제되는 형태로
리뉴얼이 진행된다면 나중에 DB이관 작업시에 하나씩 일일이 소소하게 살펴보느라 힘들다.
물론 꼼꼼한 개발자라면 변경사항을 어딘가에 기록해 두겠지만 지속적으로 변경된 사항을 관리하기란 쉽지 않다.
나도 꼼곰한 성격은 아니지만 나중에 피보기 싫어서 변경된 부분들은 어딘가에 기록을 해두는데 처음에나 열심히
적고 나중에는 귀찮아서 대충 적는다. -_- 이러다보니 리뉴얼 끝난후 DB이관하려 할때 괴로워진다.
그래서 이런 괴로움과 귀찮음을 없애고자 sql문 몇가지 만들었다. 뭐 별건 없다. 단지 원본DB와 변경DB의 테이블들을
비교해서 변경된 사항을 찾아내는것 뿐이다. 뭐..좀 시간 되면 프로시저로 만들어서 원클릭만으로
변경된 DB로 샥~ 하고 바뀌게 했음 좋겠지만 ...요게 좀 위험할것 같다. (귀찮기도 하고 -_-..)
음..원랜 NorthWind DB를 이용해서 원본DB만들고 변경DB 만들어서 sql문 실행하면 삭제된 테이블 변경된 필드..등등
이미지 캡쳐까지 해서 이뿌게 보여주려 했는데.. 귀찮다 -_-
그냥 알아서 NorthWind DB 이용해서 DB하나더 만들고 이리저리 테이블 삭제해보고 필드 변경해보고 해서 스스로
해보길 바란다. (참고로 이미 알고들 있겠지만 NorthWind 테이블 장난쳐서 망가졌다고 해도
C:\Program Files\Microsoft SQL Server\MSSQL\Install 폴더 안의 instnwnd.sql 이용해서 복구할수 있으니 걱정말고
막 이용해도 된다.)
일단! 아래 sql문을 이용하기 전에 각 DB에 view를 하나 만들어 줘야한다. 뭐하는 뷰냐면 현재 DB내의 테이블/뷰/프로시저의
정보를 가져오는 뷰인데...몇년전에 웹으로 테이블 정의서 만들겠다고 난리치면서 만들어둔 뷰이다.
(이 뷰는 꽤 유용한것 같다.. 스스로 잘 사용하고 있다 -_-) 아래는 뷰 구조이다.
CREATE VIEW dbo.viewTableInfo AS
select
distinct
A.name as [tbl_id]
,B.name as [col_id]
,C.name as [data_type]
,B.length AS [len]
,E.text as [def_val]
,B.isnullable as [all_null]
,B.autoval as [a_n]
,D.colid as [p_k]
,A.xtype
,A.id
,B.colorder [col_ord]
from
sysobjects as A
inner join syscolumns B on A.id=B.id
inner join systypes as C on C.xusertype=B.xusertype
left join sysindexkeys as D on B.id=D.id and B.colid=D.colid
left join syscomments as E on E.id=B.cdefault
where
A.xtype in ('U','V','P')
and A.status >= 0
and left(A.name,4) <> 'viewTableInfo'
별거 없다. 귀찮음을 불구하고 설명하자면 아래와 같다.
tbl_id : 테이블명
col_id : 컬럼명(필드명)
data_type : 데이타 타입
len : 데이타 길이
def_val : 기본값
all_null : 널 허용 여부
a_n : 자동증가 여부
p_k : 인덱스
xtype : U(테이블), V(뷰), P(프로시저)
id : 뭐더라 -_-.. 아마 시스템 내무적으로 인식하는 컬럼 키값일것이다.
colorder : 컬럼 순번
다시한번 말하지만 이 뷰를 변경DB, 원본DB에 각각 생성해 줘야한다. 왜냐면 비교할때 이 뷰를 읽어서 비교하기 때문이다.
아..그리고 용어를 정리하자면 원본DB (OriginalDB), 변경DB (ChangeDB) 이렇게되니 sql문 읽을때 염두해두면된다.
자! 실질적으로 비교시에 사용할 쿼리이다. 설명 없다. 그냥 본다. 휘리릭~
/***** 신규 테이블 */
select
distinct
a.tbl_id
from
ChangeDB..viewTableInfo a
where
a.xtype='U'
and a.tbl_id not in (select distinct b.tbl_id from OriginalDB..viewTableInfo b)
/***** 신규 필드 */
select
a.*
from
viewTableInfo a
left join OriginalDB..viewTableInfo b on a.tbl_id=b.tbl_id and a.col_id=b.col_id
where
a.xtype ='U'
and b.col_id is null
and a.tbl_id in (select distinct c.tbl_id from OriginalDB..viewTableInfo c)
order by a.tbl_id, a.col_ord
/***** 변경된 필드 */
select
a.tbl_id, a.col_id, a.data_type, a.len, a.def_val, a.all_null, a.a_n, a.p_k,
b.tbl_id, b.col_id, b.data_type, b.len, b.def_val, b.all_null, b.a_n, b.p_k
from
viewTableInfo a
left join OriginalDB..viewTableInfo b on a.tbl_id=b.tbl_id and a.col_id=b.col_id
where
a.xtype ='U'
and b.col_id is not null
and (a.len<>b.len
or a.data_type<>b.data_type
or a.def_val<>b.def_val
or a.all_null<>b.all_null
or a.p_k<>b.p_k
or left(isNull(a.a_n,0),2)<>left(isNull(b.a_n,0),2)
)
order by a.tbl_id, a.col_ord
/***** 삭제된 테이블 */
select
distinct
a.tbl_id
from
OriginalDB..viewTableInfo a
where
a.xtype='U'
and a.tbl_id not in (select distinct b.tbl_id from viewTableInfo b)
/***** 삭제된 필드 */
select
a.tbl_id
, a.col_id
from
OriginalDB..viewTableInfo a
left join ChangeDB..viewTableInfo c on a.tbl_id=c.tbl_id and a.col_id=c.col_id
where
a.xtype='U'
and c.col_id is null
and a.tbl_id in (select distinct b.tbl_id from viewTableInfo b)
order by a.tbl_id
