使用MTK迁移MSSQL到MogDB/openGauss

{
  "source": {
    "type": "sqlserver",
    "connect": {
      "dsn": "sqlserver://username:password@host/instance?encrypt=disable",
    }
  },
  "target": {
    "type": "mogdb",
    "connect": {
    }
  }
}

SELECT name from sys.schemas where 1=1

select schema_name(s.schema_id) as schema_name,
       s.name as seq_name,
       s.minimum_value as MINVALUE,
       s.maximum_value as MAXVALUE,
       s.[increment] as INCREMENT,
       -- '' as ORDER,
       s.is_cycling as CYCLE ,
       s.cache_size as CACHE,
       s.current_value as current_value,
       s.current_value as LAST_NUMBER,
       s.start_value as START,
       s.[precision] as PRECISION
from sys.sequences s where 1=1

select schema_name(s.schema_id) as schema_name,
       s.name as seq_name,
       s.minimum_value as MINVALUE,
       s.maximum_value as MAXVALUE,
       s.[increment] as INCREMENT,
       -- '' as ORDER,
       s.is_cycling as CYCLE ,
       s.cache_size as CACHE,
       s.current_value as current_value,
       s.last_used_value as LAST_NUMBER,
       s.start_value as START,
       s.[precision] as PRECISION
from sys.sequences s where 1=1

SELECT
s.name as schema_name,
t.name as type_name,
t.system_type_id,
t.user_type_id,
t.max_length,
t.precision,
t.scale,
s1.name as base_schema_name,
base.name as base_type_name,
t.is_nullable,
t.is_table_type,
t.is_user_defined,
t.is_assembly_type,
t.default_object_id,
t.rule_object_id
FROM sys.types as t
left join sys.schemas as s on t.schema_id = s.schema_id
left join sys.types as base on t.system_type_id = base.user_type_id
left join sys.schemas as s1 on base.schema_id = s1.schema_id
where t.is_user_defined=1

select schema_name(t.schema_id) as schema_name,t.name as table_name,0 as t.is_external,
0 as t.is_node,
0 as t.is_edge,
0 as t.temporal_type,
t.is_filetable,
ep.value,
ic.partition_ordinal
from sys.tables t
LEFT OUTER JOIN sys.extended_properties ep on ep.class = 1 AND ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id AND i.[type] <= 1
LEFT JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1
where 1=1

select schema_name(t.schema_id) as schema_name,t.name as table_name,t.is_external,
t.is_node,
t.is_edge,
t.temporal_type,
t.is_filetable,
ep.value,
ic.partition_ordinal
from sys.tables t
LEFT OUTER JOIN sys.extended_properties ep on ep.class = 1 AND ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id AND i.[type] <= 1
LEFT JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1
where 1=1

SELECT sch.name AS SchemaName,t.name AS TableName,i.name AS IndexName,p.partition_number,
 p.partition_id,
 i.data_space_id,
 f.function_id,
 f.type_desc as partitioningType,
 r.boundary_id,
 r.value AS BoundaryValue,
 ic.column_id AS PartitioningColumnID,
 c.name AS PartitioningColumnName,
 s.name as partition_scheme_name,
 f.name as partition_function_name,
 f.boundary_value_on_right
FROM
 sys.tables AS t
JOIN sys.indexes AS i ON t.object_id = i.object_id AND i.[type] <= 1
JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1
 -- because 0 = non-partitioning column
JOIN sys.columns AS c ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id
JOIN sys.partition_functions AS f ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
LEFT OUTER JOIN sys.schemas sch ON t.schema_id = sch.schema_id
WHERE 1=1

SELECT
 s.Name AS TABLE_SCHEMA,
 t.NAME AS TABLE_NAME,
 p.partition_id ,
 a.type_desc ,
 p.rows AS RowCounts,
 a.used_pages * 8 as UsedSpaceKB,
 ic.partition_ordinal,
 t.max_column_id_used
FROM
 sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id and i.[type]  <= 1 -- 只统计堆/聚集索引
LEFT JOIN sys.index_columns AS ic ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND t.type = 'U'

SELECT schema_name(tab.schema_id) as schema_name,tab.name as table_name,c.name as column_name,
    c.column_id,
    t.name as data_type,
    c.max_length,
    c.precision,
    c.scale,
    0 as c.is_hidden,
    c.is_nullable ,
    0 as c.is_masked,
    COLUMNPROPERTY(c.object_id,c.name,'charmaxlen') as char_max_length,
    dc.definition as default_definition,
    ep.value as description,
    cc.definition,
    c.is_identity ,
    ic.seed_value ,
    ic.last_value ,
    ic.increment_value,
    t.is_user_defined
FROM
 sys.all_columns c
left join sys.types as t on c.user_type_id = t.user_type_id
JOIN sys.tables tab ON tab.object_id = c.object_id
LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = tab.object_id AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN master.sys.extended_properties ep ON ep.class = 1 AND ep.major_id = tab.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
left join sys.computed_columns cc on cc.object_id = tab.object_id  and cc.column_id = c.column_id
left join sys.identity_columns ic on ic.object_id = tab.object_id  and ic.column_id = c.column_id
where 1=1

SELECT schema_name(tab.schema_id) as schema_name,tab.name as table_name,c.name as column_name,
    c.column_id,
    t.name as data_type,
    c.max_length,
    c.precision,
    c.scale,
    c.is_hidden,
    c.is_nullable ,
    c.is_masked,
    COLUMNPROPERTY(c.object_id,c.name,'charmaxlen') as char_max_length,
    dc.definition as default_definition,
    ep.value as description,
    cc.definition,
    c.is_identity ,
    ic.seed_value ,
    ic.last_value ,
    ic.increment_value,
    t.is_user_defined
FROM
 sys.all_columns c
left join sys.types as t on c.user_type_id = t.user_type_id
JOIN sys.tables tab ON tab.object_id = c.object_id
LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = tab.object_id AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN master.sys.extended_properties ep ON ep.class = 1 AND ep.major_id = tab.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
left join sys.computed_columns cc on cc.object_id = tab.object_id  and cc.column_id = c.column_id
left join sys.identity_columns ic on ic.object_id = tab.object_id  and ic.column_id = c.column_id
where 1=1

select schema_name(t.schema_id) as schema_name,t.[name] as table_name,
 case
  when t.[type] = 'U' then 'Table'
  when t.[type] = 'V' then 'View'
 end as [object_type],
 case
  when c.[type] = 'PK' then 'P'
  -- 'Primary key'
  when c.[type] = 'UQ' then 'U'
  --'Unique constraint'
  when i.[type] = 1 then 'Unique clustered index'
  when i.[type] = 2 then 'Unique index'
 end as constraint_type,
 c.name as constraint_name,
 i.name as index_name,
 substring(column_names, 1, len(column_names)-1) as columns
from
 sys.objects t
inner join sys.indexes i on
 t.object_id = i.object_id
inner join sys.key_constraints c on
 i.object_id = c.parent_object_id
 and i.index_id = c.unique_index_id cross apply (
 select
   CAST(ic.key_ordinal AS varchar) + '||||' +
  col.[name] + '||||' +
  case
   when ic.is_descending_key = 1 then 'desc'
   else 'asc'
  end + '||||' +
  case
   when col.is_nullable = 1 then '1'
   else '0'
  end +
  '@ '
 from
  sys.index_columns ic
 inner join sys.columns col on
  ic.object_id = col.object_id
  and ic.column_id = col.column_id
 where
  ic.object_id = t.object_id
  and ic.index_id = i.index_id
 order by
  ic.key_ordinal for xml path ('') ) D (column_names)
where
 is_unique = 1
 and t.is_ms_shipped <> 1
 and t.[type] = 'U'

select schema_name(t.schema_id) as schema_name,t.[name] as table_name,'Table',
  'C', -- Check constraint
  con.[name] as constraint_name,
  col.name ,
  con.[definition] as SEARCH_CONDITION
 from
  sys.check_constraints con
 left outer join sys.objects t on
  con.parent_object_id = t.object_id
 left outer join sys.all_columns col on
  con.parent_column_id = col.column_id
  and con.parent_object_id = col.object_id

select fk.object_id,schema_name(fk_tab.schema_id) as owner,fk_tab.name table_name,
 fk.name as constraint_name,
 fk.is_disabled as con_is_disabled,
 fk.delete_referential_action_desc ,
 fk.update_referential_action_desc ,
    schema_name(pk_tab.schema_id) as R_OWNER,
    pk_tab.name as R_TABLE_NAME,
    fk_cols.constraint_column_id as no,
    fk_col.name as fk_column_name,
    fk_col.is_nullable as fk_column_is_nullable,
    pk_col.name as pk_column_name,
    pk_col.is_nullable as pk_column_is_nullable
from sys.foreign_keys fk
    inner join sys.tables fk_tab
        on fk_tab.object_id = fk.parent_object_id
    inner join sys.tables pk_tab
        on pk_tab.object_id = fk.referenced_object_id
    inner join sys.foreign_key_columns fk_cols
        on fk_cols.constraint_object_id = fk.object_id
    inner join sys.columns fk_col
        on fk_col.column_id = fk_cols.parent_column_id
        and fk_col.object_id = fk_tab.object_id
    inner join sys.columns pk_col
        on pk_col.column_id = fk_cols.referenced_column_id
        and pk_col.object_id = pk_tab.object_id

select schema_name(t.schema_id) as schema_name,t.[name] as tab_name,i.[name] as index_name,
 substring(column_names, 1, len(column_names)-1) as [columns],
-- case
--  when i.[type] = 1 then 'Clustered index'
--  when i.[type] = 2 then 'Nonclustered unique index'
--  when i.[type] = 3 then 'XML index'
--  when i.[type] = 4 then 'Spatial index'
--  when i.[type] = 5 then 'Clustered columnstore index'
--  when i.[type] = 6 then 'Nonclustered columnstore index'
--  when i.[type] = 7 then 'Nonclustered hash index'
-- end as index_type,
 i.[type] as index_type,
 i.is_unique,
-- case
--  when i.is_unique = 1 then 'Unique'
--  else 'Not unique'
-- end as [unique],
 t.[type] as object_type,
 i.is_disabled
-- case
--  when t.[type] = 'U' then 'Table'
--  when t.[type] = 'V' then 'View'
-- end as [object_type]
from
 sys.objects t
inner join sys.indexes i on
 t.object_id = i.object_id cross apply (
 select
  CAST(ic.key_ordinal AS varchar) + '||||' + col.[name] + '||||' +
  case
   when ic.is_descending_key = 1 then 'desc'
   else 'asc'
  end + '||||' +
  case
   when col.is_nullable = 1 then '1'
   else '0'
  end + '@ '
 from
  sys.index_columns ic
 inner join sys.columns col on
  ic.object_id = col.object_id
  and ic.column_id = col.column_id
 where
  ic.object_id = t.object_id
  and ic.index_id = i.index_id
 order by
  key_ordinal for xml path ('') ) D (column_names)
where
 t.is_ms_shipped <> 1
 and index_id > 0
 and i.is_primary_key = 0
 and i.is_unique_constraint = 0

select schema_name(v.schema_id), v.name, m.definition from
 sys.views v join sys.sql_modules m on m.object_id = v.object_id
 where 1=1

select
 schema_name(obj.schema_id) as schema_name,
 obj.object_id,
 obj.name as function_name,
 type,
 mod.definition
from
 sys.objects obj
join sys.sql_modules mod on mod.object_id = obj.object_id
where 1=1 AND obj.[type] in
('AF','FN','FS','FT','IF','TF')

select
 schema_name(obj.schema_id) as schema_name,
 obj.object_id,
 obj.name as function_name,
 type,
 mod.definition
from
 sys.objects obj
join sys.sql_modules mod on mod.object_id = obj.object_id
where 1=1 AND obj.[type] in
('P','PC','RF','X')

select t.object_id,
 schema_name(o.schema_id) as trigger_owner,
 t.name as trigger_name,
 schema_name(tab.schema_id) as table_owner,
 tab.name table_name,
 t.is_disabled ,
 --  te.is_trigger_event ,
 case when is_instead_of_trigger = 1 then 'INSTEAD OF' else 'AFTER' end as [activation],
 case when t.parent_class = 1 then 'TABLE' when t.parent_class = 0 then 'DATABASE' end [class],
 --  te.type_desc ,
 mod.definition
from
 sys.triggers t
 -- join sys.trigger_events te on t.object_id = te.object_id
join sys.all_objects o on o.object_id = t.object_id
join sys.all_objects tab on tab.object_id = t.parent_id
join sys.sql_modules mod on mod.object_id = t.object_id
where 1=1