select *
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_SCHEMA = 'prj_carleyfoundry_import'
order by c.TABLE_NAME, c.ORDINAL_POSITION

select c.DATA_TYPE,
group_concat(concat(c.TABLE_NAME, '.', c.COLUMN_NAME) separator ', ')
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_SCHEMA = 'prj_carleyfoundry_import'
group by c.DATA_TYPE




SET SESSION group_concat_max_len = 16500;
select 
--    c.TABLE_NAME,
    concat(
        'create table [',
        c.TABLE_NAME,
        '] (',
        group_concat(
            concat(
                '[',
                c.COLUMN_NAME, 
                '] ',
                case c.DATA_TYPE
                    when 'char' then concat('nchar(', c.CHARACTER_MAXIMUM_LENGTH, ')')
                    when 'varchar' then concat('nvarchar(', c.CHARACTER_MAXIMUM_LENGTH, ')')
                    when 'text' then 'nvarchar(max)'
                    when 'mediumtext' then 'nvarchar(max)'
                    when 'mediumblob' then 'varbinary(max)'
                    when 'enum' then concat('nvarchar(100)')
                    else c.DATA_TYPE
                end,
                ' ',
                if(c.IS_NULLABLE = 'NO', 'not null', 'null'),
                if(
                    c.EXTRA != 'auto_increment' and c.COLUMN_DEFAULT is not null, 
                    concat(
                        ' default ', 
                        if(
                            c.DATA_TYPE in ('int', 'float'),
                            c.COLUMN_DEFAULT,
                            concat('\'', c.COLUMN_DEFAULT, '\'')
                        ),
                        ' '
                    ), 
                    ''
                ),
                if(c.DATA_TYPE = 'int' and c.EXTRA = 'auto_increment', ' identity(1,1) ', ' '),
                if(c.DATA_TYPE = 'enum', concat(' check([', c.COLUMN_NAME, '] in ', substring(c.COLUMN_TYPE, 5), ' or [', c.COLUMN_NAME, '] = \'\') '), '')
            )
            order by c.ORDINAL_POSITION
            separator ', '
        ),
        if(
            pr.primaries is not null,
            concat(
                ', primary key(',
                pr.primaries,
                ')'
            ),
            ''
        ),
        ');'
    )
from INFORMATION_SCHEMA.COLUMNS c
left join (
    select 
        pri.TABLE_SCHEMA, 
        pri.TABLE_NAME,
        group_concat(concat('[', pri.COLUMN_NAME, ']') order by pri.ORDINAL_POSITION separator ', ') primaries
    from INFORMATION_SCHEMA.COLUMNS pri 
    where pri.TABLE_SCHEMA = 'prj_carleyfoundry_import'
        and pri.COLUMN_KEY = 'PRI'
    group by pri.TABLE_SCHEMA, pri.TABLE_NAME
) pr on pr.TABLE_SCHEMA = c.TABLE_SCHEMA and pr.TABLE_NAME = c.TABLE_NAME
where c.TABLE_SCHEMA = 'prj_carleyfoundry_import'
group by c.TABLE_SCHEMA, c.TABLE_NAME




select 
    concat(
        'set identity_insert [', 
        c.TABLE_NAME, 
        '] on\nGO\n\n',

        'insert into [',
        c.TABLE_NAME,
        '] (',
        group_concat(concat('[', c.COLUMN_NAME, ']') order by c.ORDINAL_POSITION separator ', '),
        ') select ',
        group_concat(concat('[', c.COLUMN_NAME, ']') order by c.ORDINAL_POSITION separator ', '),
        ' from openquery(CARLEYF, \'select * from ',
        c.TABLE_NAME, 
        '\');\nGO\n\n',

        'set identity_insert [', 
        c.TABLE_NAME, 
        '] off\nGO\n\n'
    )
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_SCHEMA = 'prj_carleyfoundry_import'
group by c.TABLE_SCHEMA, c.TABLE_NAME




select *
from INFORMATION_SCHEMA.STATISTICS s
where s.TABLE_SCHEMA = 'prj_carleyfoundry_import'


select 
    concat(
        'create ',
        if(s.NON_UNIQUE = 0, 'unique ', ''),
        'index [',
        if(s.NON_UNIQUE = 0, 'UQ__', 'IDX__'),
        s.TABLE_NAME,
        '__',
        s.INDEX_NAME,
        '] on [',
        s.TABLE_NAME,
        '](',
        group_concat(concat('[', s.COLUMN_NAME, ']') order by s.SEQ_IN_INDEX separator ', '),
        ')',
        if(
            s.NON_UNIQUE = 0,
            concat(
                ' where ', 
                group_concat(concat('[', s.COLUMN_NAME, '] is not null') order by s.SEQ_IN_INDEX separator ' and ')
            ),
            ''
        ),
        ';'
    )
from INFORMATION_SCHEMA.STATISTICS s
where s.TABLE_SCHEMA = 'prj_carleyfoundry_import'
    and s.INDEX_NAME != 'PRIMARY'
group by s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME


/*
from INFORMATION_SCHEMA.STATISTICS s
left join INFORMATION_SCHEMA.COLUMNS c on s.TABLE_SCHEMA = c.TABLE_SCHEMA 
    and s.TABLE_NAME = c.TABLE_NAME 
    and s.COLUMN_NAME = c.COLUMN_NAME 
    and c.COLUMN_KEY = 'PRI'
where s.TABLE_SCHEMA = 'prj_carleyfoundry_import'
    and c.COLUMN_NAME is null
group by s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
*/


select
    concat(
        'select \'',
        t.TABLE_NAME,
        '\', count(*) from ',
        t.TABLE_NAME,
        ';'
    )

from INFORMATION_SCHEMA.TABLES t
where t.TABLE_SCHEMA = 'prj_carleyfoundry_import'
order by t.TABLE_NAME