Dbeaver做数据迁移的详细过程记录

　　WITH indexInfo as (

　　SELECT SCHEMA_NAME(t.schema_id) [schema_name],t.name as [table_name],t1.name as [index_name]

　　,t1.type,t1.type_desc,t1.is_unique,t1.is_primary_key,t1.is_unique_constraint,t1.has_filter,t1.filter_definition

　　,STUFF((SELECT ','+t4.name FROM sys.sysindexkeys t2

　　inner join sys.index_columns t3 ON t2.id=t3.object_id and t2.indid=t3.index_id and t2.colid=t3.column_id

　　inner join sys.syscolumns t4 ON t2.id=t4.id and t2.colid=t4.colid

　　WHERE t2.id=t1.object_id and t1.index_id=t2.indid and t2.keyno <> 0 ORDER BY t3.key_ordinal FOR XML PATH('')),1,1,'') AS index_cols

　　,STUFF((SELECT ','+t4.name FROM sys.sysindexkeys t2

　　inner join sys.index_columns t3 ON t2.id=t3.object_id and t2.indid=t3.index_id and t2.colid=t3.column_id

　　inner join sys.syscolumns t4 ON t2.id=t4.id and t2.colid=t4.colid

　　WHERE t2.id=t1.object_id and t1.index_id=t2.indid and t2.keyno = 0 ORDER BY t3.key_ordinal FOR XML PATH('')),1,1,'') AS include_cols

　　FROM sys.tables as t

　　inner join sys.indexes as t1 on (t1.index_id > 0 and t1.is_hypothetical = 0) and (t1.object_id=t.object_id)

　　WHERE t1.type in(1,2)

　　), indexInfo2 AS (

　　SELECT * ,(CASE

　　WHEN is_primary_key = 1

　　THEN 'alter table '+[schema_name]+'.'+[table_name]+' add constraint '+[index_name]+' primary key '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+'('+index_cols+');'

　　WHEN is_unique = 1 AND is_unique_constraint = 1

　　THEN 'alter table '+[schema_name]+'.'+[table_name]+' add constraint '+[index_name]+' unique '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+'('+index_cols+');'

　　WHEN is_unique = 1 AND (is_primary_key = 0 OR is_unique_constraint = 0)

　　THEN 'create unique '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+' index '+[index_name]+' on '+[schema_name]+'.'+[table_name]+'('+index_cols+');'

　　ELSE 'create '+(CASE WHEN [type]=1 THEN 'clustered' ELSE 'nonclustered' END)+' index '+[index_name]+' on '+[schema_name]+'.'+[table_name]+'('+index_cols+') ;'

　　END) script

　　FROM indexInfo

　　) SELECT [schema_name],[table_name],[index_name],script

　　+(CASE WHEN include_cols IS NOT NULL THEN ' include('+include_cols+')' ELSE '' END)

　　+(CASE WHEN has_filter = 1THEN ' where '+filter_definition ELSE '' END)

　　FROM indexInfo2

　　ORDER BY [schema_name],[table_name],[type],[index_name],is_primary_key DESC,is_unique_constraint DESC,is_unique DESC