Life in Downunder

最近在寫 database archiving的程式，其中一個步驟，是要重建已經做過 archiving的 table的 index。SQL 2005的 database scripting功能比起 SQL 2000並沒有比較高明，用起來礙手礙腳。想要單獨 scripting index卻做不到，只好自己動手寫了。

Scripting database object並不難寫，只要善用 system view即可。印象中，SQL server online help並沒有 system view的欄位說明，卻有 ER model可供下載，但是眾多的 system view擠在一張 A4大小的 PDF檔裡，誰看得清楚之間的關係啊。

要組合出 index DDL並不難，網路上也有些現成的 script可以參考，但是大部分都是用 cursor寫的。打從 SQL 2005開始支援 CTE(common table expression)之後，很多過去只能用 cursor做到的功能，現在都能用 CTE達成，簡單明瞭。以下就是我寫的 script。

with IndexDef
as (
select
idc.index_id, idc.index_column_id,
case idx.is_unique when 1 then 'UNIQUE ' else '' end + idx.type_desc collate SQL_Latin1_General_CP1_CI_AS as index_type,
idx.name as index_name,tab.name as table_name,dsp.name as filegroup_name,
col.name+case when idc.is_descending_key=1 then ' DESC' else '' end as column_name
from sys.index_columns idc
join sys.indexes idx
on idx.index_id = idc.index_id
and idx.object_id = idc.object_id
join sys.data_spaces dsp
on dsp.data_space_id = idx.data_space_id
join sys.columns col
on col.column_id = idc.column_id
and col.object_id = idc.object_id
join sys.tables tab
on tab.object_id = col.object_id
)
select table_name,index_name,'CREATE '+index_type+' INDEX '+index_name+' ON '+table_name+'('+left(column_list, Len(column_list)-1)+') ON '+filegroup_name as index_script
from (
SELECT a1.index_name, a1.index_type, a1.table_name, a1.filegroup_name,
( SELECT cast(a2.column_name as varchar(100)) + ','
FROM IndexDef a2
WHERE a2.table_name = a1.table_name
and a2.index_name = a1.index_name
ORDER BY a2.index_id,a2.index_column_id
FOR XML PATH('') ) AS column_list
FROM IndexDef a1
GROUP BY a1.index_name, a1.index_type, a1.table_name, a1.filegroup_name
) src