For XML Path

SQL Server 中利用這個語法可以將查詢的結果轉換成XML data,常用的語法如下

SELECT ns_signname
FROM signorg
WHERE signorg.seqsn= 111
FOR XML PATH('')

這個語法會將所有查詢結果組成一個字串,效果就像之前的string concatenation

詳細說明可以參考http://it.dianping.com/sql-for-xml-path.htm

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))

嗯,果然跳出一堆...

MCSE chapter 5 sec 1