Source to Target Generator

/*
    Generate source to target mapping to be used with sisula-ETL.

    VERSION HISTORY
    2017-04-28  Lars Rönnbäck         CREATED
    2017-05-02  Lars Rönnbäck         Added @schema.
                                        Handles multiple versions.
                                        Smarter code for historized columns and knots.
    2017-05-04  Lars Rönnbäck         Added @naturalKeyColumns.
                                        Added construct attribtues to indicate the type of the target.

*/

declare @targetDatabase varchar(555) = 'DW';
declare @targetSchema varchar(555) = 'dbo';
declare @sourceChangingTimeColumn varchar(555) = 'Modified_Date'; -- or null for no historization
declare @naturalKeyColumns varchar(555) = 'SourceCol1, SourceCol2';

if object_id('tempdb..#attributes') is not null
drop table #attributes;

create table #attributes (
    name varchar(555),
    capsule varchar(555),
    mnemonic char(3),
    descriptor varchar(555),
    anchorMnemonic char(2),
    anchorDescriptor varchar(555),
    timeRange varchar(555),
    knotName varchar(555),
    knotCapsule varchar(555),
    knotMnemonic char(3),
    knotDescriptor varchar(555),
    major_id int not null primary key
);

declare @sql varchar(max);
set @sql = '
        select
            a.name,
            a.capsule,
            a.mnemonic,
            a.descriptor,
            a.anchorMnemonic,
            a.anchorDescriptor,
            a.timeRange,
            k.name as knotName,
            k.capsule as knotCapsule,
            k.mnemonic as knotMnemonic,
            k.descriptor as knotDescriptor,
            OBJECT_ID(a.capsule + ''.'' + a.name) as major_id
        from
            ' + @targetSchema + '._Attribute a
        left join
            ' + @targetSchema + '._Knot k
        on
            k.mnemonic = a.knotRange
        and
            k.version = a.version
        where
            a.version = (select top 1 version from ' + @targetDatabase + '.' + @targetSchema + '._Schema order by version desc)
';

insert into #attributes exec (@sql);

with attributes as (
    select
        'l' + k.anchorMnemonic + '_' + k.anchorDescriptor as lView,
        k.anchorMnemonic + '_' + k.anchorDescriptor as anchorName,
        k.name as attributeColumn,
        k.name as attributeName,
        k.anchorMnemonic + '_' + k.mnemonic + '_' + k.knotName as knotColumn,
        k.knotName,
        case
            when @sourceChangingTimeColumn is not null and k.timeRange is not null
            then k.anchorMnemonic + '_' + k.mnemonic + '_ChangedAt'
        end as changingColumn,
        ep.*
    from (
        select
            metadata,
            sourceTable,
            sourceColumn,
            major_id
        from -- select * from
            sys.extended_properties
        outer apply (
            values (
                cast([value] as nvarchar(max))
            )
        ) d ([description])
        outer apply (
            values (
                left([description], patindex('%Column%', [description]) - 1),
                substring([description], patindex('%Table%', [description]) + 7, 100000000),
                substring(
                    [description],
                    patindex('%Column%', [description]) + 8,
                    charindex(CHAR(10), [description], patindex('%Column%', [description]) + 8)
                    - 1
                    - (patindex('%Column%', [description]) + 8)
                )
            )
        ) e (metadata, sourceTable, sourceColumn)
        where
            [description] like '%Column%'
    ) ep
    join
        #attributes k
    on
        ep.major_id = k.major_id
)
select
    'Autogenerated' as [@name],
    @targetDatabase as [@database],
    (
        select
            sourceTable as [@source],
            lView as [@target],
            anchorName as [@anchor],
            (
                select
                    a.sourceColumn as [@source],
                    isnull(a.knotColumn, a.attributeColumn) as [@target],
                    case when @naturalKeyColumns like '%' + a.sourceColumn + '%' then 'natural key' end as [@as],
                    a.attributeName as [@attribute],
                    a.knotName as [@knot]
                from
                    attributes a
                where
                    a.lView = t.lView
                and
                    a.sourceTable = t.sourceTable
                for xml path('map'), type
            ),
            (
                select
                    @sourceChangingTimeColumn as [@source],
                    a.changingColumn as [@target]
                from
                    attributes a
                where
                    a.lView = t.lView
                and
                    a.sourceTable = t.sourceTable
                and
                    a.changingColumn is not null
                for xml path('map'), type
            )
        from
            (select distinct sourceTable, lView, anchorName from attributes) t
        for xml path('load'), type
    )
for xml path('target');