----------------------------------------------------------
-- Grab list of tables
----------------------------------------------------------
if object_id( 'tempdb..#tmpTables' ) is not null drop table #tmpTables
create table #tmpTables
(
    ordinal                 int identity(1,1),
    table_catalog           sysname,
    table_schema            sysname,
    table_name              sysname,
    Sequence                int default(1),
    SequenceNbr             int NULL,
    HasIdentityCol          bit default(0)
)
insert into #tmpTables ( table_catalog, table_schema, table_name )
    select
        t.table_catalog,
        t.table_schema,
        t.table_name
        from information_schema.tables t (nolock)

----------------------------------------------------------
-- Load temp tables with table/column/constraint data from information_schema views
----------------------------------------------------------
if object_id( 'tempdb..#tmpColumns' ) is not null drop table #tmpColumns
select i.* into #tmpColumns from information_schema.columns i join #tmpTables t on i.table_catalog = t.table_catalog and i.table_schema = t.table_schema and i.table_name = t.table_name
create clustered index ix_tmpColumns_csn on #tmpColumns ( table_catalog, table_schema, table_name, column_name )

if object_id( 'tempdb..#tmpTblConstraints' ) is not null drop table #tmpTblConstraints
select i.* into #tmpTblConstraints from information_schema.table_constraints i join #tmpTables t on i.table_catalog = t.table_catalog and i.table_schema = t.table_schema and i.table_name = t.table_name
create clustered index ix_tmpTblConstraints_csn on #tmpTblConstraints ( table_catalog, table_schema, table_name )
create index ix_tmpTblConstraints_c on #tmpTblConstraints ( constraint_catalog, constraint_schema, constraint_name )

if object_id( 'tempdb..#tmpKeyColUsage' ) is not null drop table #tmpKeyColUsage
select i.* into #tmpKeyColUsage from information_schema.key_column_usage i join #tmpTables t on i.table_catalog = t.table_catalog and i.table_schema = t.table_schema and i.table_name = t.table_name
create clustered index ix_tmpKeyColUsage_csn on #tmpKeyColUsage ( table_catalog, table_schema, table_name, column_name )

if object_id( 'tempdb..#tmpRefConstraints' ) is not null drop table #tmpRefConstraints
select i.* into #tmpRefConstraints
    from information_schema.referential_constraints i
    join #tmpTblConstraints c
        on (    i.constraint_catalog = c.constraint_catalog
            and i.constraint_schema = c.constraint_schema
            and i.constraint_name = c.constraint_name )
        or (    i.unique_constraint_catalog = c.constraint_catalog
            and i.unique_constraint_schema = c.constraint_schema
            and i.unique_constraint_name = c.constraint_name )
create clustered index ix_tmpRefConstraints_c on #tmpRefConstraints ( unique_constraint_catalog, unique_constraint_schema, unique_constraint_name, constraint_catalog, constraint_schema, constraint_name )

----------------------------------------------------------
-- Check for the existance of LastUpdatedBy, UpdateDate and Identity columns
----------------------------------------------------------
update #tmpTables
    set
        HasIdentityCol      = case when i.column_name is not null then 1 else 0 end
        from #tmpTables t (nolock)
        left join #tmpColumns i (nolock)
            on t.table_catalog = i.table_catalog
            and t.table_schema = i.table_schema
            and t.table_name = i.table_name
            and columnproperty( object_id( i.table_name ), i.column_name, 'IsIdentity' ) = 1

----------------------------------------------------------
-- Update load sequence based on dependencies
----------------------------------------------------------
while( @@rowcount > 0 )
    update #tmpTables
        set Sequence = s.Sequence + 1
        from #tmpTables fkt
        join (
            select
                table_catalog   = fk.table_catalog,
                table_schema    = fk.table_schema,
                table_name      = fk.table_name,
                Sequence        = max( pkt.Sequence )
                from #tmpTblConstraints fk (nolock)
                join #tmpRefConstraints r (nolock)
                    on r.constraint_catalog = fk.constraint_catalog
                    and r.constraint_schema = fk.constraint_schema
                    and r.constraint_name = fk.constraint_name
                join #tmpTblConstraints pk (nolock)
                    on r.unique_constraint_catalog = pk.constraint_catalog
                    and r.unique_constraint_schema = pk.constraint_schema
                    and r.unique_constraint_name = pk.constraint_name
                    and pk.constraint_type = 'primary key'
                join #tmpTables pkt
                    on pk.table_catalog = pkt.table_catalog
                    and pk.table_schema = pkt.table_schema
                    and pk.table_name = pkt.table_name
                where fk.constraint_type = 'foreign key'
                and not
                    ( fk.table_catalog = pk.table_catalog
                    and fk.table_schema = pk.table_schema
                    and fk.table_name = pk.table_name
                    )
                group by fk.table_catalog, fk.table_schema, fk.table_name
            ) s
            on fkt.table_catalog = s.table_catalog
            and fkt.table_schema = s.table_schema
            and fkt.table_name = s.table_name
            and fkt.Sequence = s.Sequence

update #tmpTables
    set SequenceNbr = s.SequenceNbr
    from #tmpTables t
    join (
        select
            table_catalog,
            table_schema,
            table_name,
            SequenceNbr = rank() over( order by Sequence, table_catalog, table_schema, table_name )
        from #tmpTables
        ) s on t.table_name = s.table_name

----------------------------------------------------------
-- Load temp tables with concattenated PK column data
----------------------------------------------------------
if object_id( 'tempdb..#tmpPKCols' ) is not null drop table #tmpPKCols
;with
pkcols
as
(
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name,
        ordinal         = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position ),
        reverse_ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position desc )
        from #tmpColumns c (nolock)
        join #tmpKeyColUsage kc (nolock)
            on c.table_catalog = kc.table_catalog
            and c.table_schema = kc.table_schema
            and c.table_name = kc.table_name
            and c.column_name = kc.column_name
        join #tmpTblConstraints tc (nolock)
            on kc.table_catalog = tc.table_catalog
            and kc.table_schema = tc.table_schema
            and kc.table_name = tc.table_name
            and kc.constraint_name = tc.constraint_name
        join #tmpTables t (nolock)
            on tc.table_catalog = t.table_catalog
            and tc.table_schema = t.table_schema
            and tc.table_name = t.table_name
        where tc.constraint_type = 'primary key'
)
select * into #tmpPKCols from pkcols order by ordinal
if object_id( 'tempdb..#tmpConcatPKCols' ) is not null drop table #tmpConcatPKCols
;with concatpkcols
as
(
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
        JoinOnPKCols        = convert( varchar(max), '{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
        ConcatPKCols        = convert( varchar(max), 'coalesce( convert( varchar, ' + c.column_name + ' ), '''''''' )' ),
        AllPKCols           = convert( varchar(max), '{source_alias}' + c.column_name )
        from #tmpPKCols c
        where c.ordinal = 1
    union all
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
        JoinOnPKCols        = convert( varchar(max), cpc.JoinOnPKCols + '{wrap}{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
        ConcatPKCols        = convert( varchar(max), cpc.ConcatPKCols + '{wrap}+ char(1) + coalesce( convert( varchar, ' + c.column_name + ' ), '''''''' )' ),
        AllPKCols           = convert( varchar(max), cpc.AllPKCols + '{wrap}{source_alias}' + c.column_name )
        from #tmpPKCols c
        join concatpkcols cpc
            on c.table_catalog = cpc.table_catalog
            and c.table_schema = cpc.table_schema
            and c.table_name = cpc.table_name
            and c.ordinal = cpc.ordinal + 1
)
select * into #tmpConcatPKCols from concatpkcols where reverse_ordinal = 1

----------------------------------------------------------
-- Load temp tables with concattenated Non-PK column data
----------------------------------------------------------
if object_id( 'tempdb..#tmpNonPKCols' ) is not null drop table #tmpNonPKCols
;with nonpkcols
as
(
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name,
        ordinal         = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position ),
        reverse_ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position desc )
        from #tmpColumns c (nolock)
        join #tmpTables t (nolock)
            on c.table_catalog = t.table_catalog
            and c.table_schema = t.table_schema
            and c.table_name = t.table_name
        left join #tmpPKCols pk
            on c.table_catalog = pk.table_catalog
            and c.table_schema = pk.table_schema
            and c.table_name = pk.table_name
            and c.column_name = pk.column_name
        where pk.column_name is null and c.data_type not in ( 'text', 'image' )
)
select * into #tmpNonPKCols from nonpkcols order by ordinal
if object_id( 'tempdb..#tmpConcatNonPKCols' ) is not null drop table #tmpConcatNonPKCols
;with concatnonpkcols
as
(
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
        DiffOnNonPKCols = convert( varchar(max), 'where nullif( {source_alias_1}' + c.column_name + ', {source_alias_2}' + c.column_name + ' ) is not null or nullif( {source_alias_2}' + c.column_name + ', {source_alias_1}' + c.column_name + ' ) is not null' ),
        UpdateNonPKCols = convert( varchar(max), c.column_name + ' = a.' + c.column_name ),
        JoinOnNonPKCols = convert( varchar(max), '{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
        ConcatNonPKCols = convert( varchar(max), 'coalesce( convert( varchar, ' + c.column_name + ' ) , '''''''' )' ),
        AllNonPKCols    = convert( varchar(max), '{source_alias}' + c.column_name )
        from #tmpNonPKCols c
        where c.ordinal = 1
    union all
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
        DiffOnNonPKCols = convert( varchar(max), cnpc.DiffOnNonPKCols + '{wrap} or nullif( {source_alias_1}' + c.column_name + ', {source_alias_2}' + c.column_name + ' ) is not null or nullif( {source_alias_2}' + c.column_name + ', {source_alias_1}' + c.column_name + ' ) is not null' ),
        UpdateNonPKCols = convert( varchar(max), cnpc.UpdateNonPKCols + '{wrap}' + c.column_name + ' = a.' + c.column_name ),
        JoinOnNonPKCols = convert( varchar(max), cnpc.JoinOnNonPKCols + '{wrap}{source_alias_1}' + c.column_name + ' = {source_alias_2}' + c.column_name ),
        ConcatNonPKCols = convert( varchar(max), cnpc.ConcatNonPKCols + '{wrap}+ char(1) + coalesce( convert( varchar, ' + c.column_name + ' ), '''''''' )' ),
        AllNonPKCols    = convert( varchar(max), cnpc.AllNonPKCols + '{wrap}{source_alias}' + c.column_name )
        from #tmpNonPKCols c
        join concatnonpkcols cnpc
            on c.table_catalog = cnpc.table_catalog
            and c.table_schema = cnpc.table_schema
            and c.table_name = cnpc.table_name
            and c.ordinal = cnpc.ordinal + 1
)
select * into #tmpConcatNonPKCols from concatnonpkcols where reverse_ordinal = 1

----------------------------------------------------------
-- Load temp tables with concattenated All column data
----------------------------------------------------------
if object_id( 'tempdb..#tmpAllCols' ) is not null drop table #tmpAllCols
;with allcols
as
(
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name,
        ordinal         = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position ),
        reverse_ordinal = rank() over ( partition by c.table_name order by c.table_name, c.ordinal_position desc )
        from #tmpColumns c (nolock)
        join #tmpTables t (nolock)
            on c.table_catalog = t.table_catalog
            and c.table_schema = t.table_schema
            and c.table_name = t.table_name
)
select * into #tmpAllCols from allcols
if object_id( 'tempdb..#tmpConcatAllCols' ) is not null drop table #tmpConcatAllCols
;with concatallcols
as
(
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
        AllCols         = convert( varchar(max), '{source_alias}' + c.column_name )
        from #tmpAllCols c
        where c.ordinal = 1
    union all
    select
        c.table_catalog, c.table_schema, c.table_name, c.column_name, c.ordinal, c.reverse_ordinal,
        AllCols         = convert( varchar(max), cac.AllCols + '{wrap}{source_alias}' + c.column_name )
        from #tmpAllCols c
        join concatallcols cac
            on c.table_catalog = cac.table_catalog
            and c.table_schema = cac.table_schema
            and c.table_name = cac.table_name
            and c.ordinal = cac.ordinal + 1
)
select * into #tmpConcatAllCols from concatallcols where reverse_ordinal = 1

select * from #tmpTables
select * from #tmpColumns
select * from #tmpTblConstraints
select * from #tmpKeyColUsage
select * from #tmpRefConstraints
select * from #tmpPKCols
select * from #tmpConcatPKCols
select * from #tmpNonPKCols
select * from #tmpConcatNonPKCols
select * from #tmpAllCols
select * from #tmpConcatAllCols