Schema Comparison

最近接了一個需要維護的系統,接到的時候傻眼,測試區與上線區的schema是不一樣的,而且沒有文件註明哪裏有新增或是修改過,沒關係,天助自助者,寫個script來看看吧!

首先查詢看看測試區的table名稱

select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

將結果暫存後,再根據每個table名稱(暫存入參數@table),查詢是否有column的設定不一致的

SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS as col
WHERE TABLE_NAME = @table and (
col.COLUMN_NAME not in (
select COLUMN_NAME
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table) or
col.DATA_TYPE <> (
select DATA_TYPE
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME) or
col.CHARACTER_MAXIMUM_LENGTH <> (
select CHARACTER_MAXIMUM_LENGTH
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME) or
col.COLUMN_DEFAULT <> (
select COLUMN_DEFAULT
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME) or
col.IS_NULLABLE <> (
select IS_NULLABLE
from 上線區資料庫.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @table and COLUMN_NAME = col.COLUMN_NAME))

嗯,果然跳出一堆...