Knot Loading Generator

/*
    Generate a knot loading script.

    VERSION HISTORY
    2017-04-24  Lars Rönnbäck         CREATED

*/

declare @sourceDatabase varchar(555) = 'Staging';
declare @sourceSchema   varchar(555) = 'dbo';

select
    '/* ----------------------------------------------------------------------------' + CHAR(10) +
    'Knot: ' + k.[name] + CHAR(10) +
    ep.metadata +
    '*/' +
    replace(
        ep.query,
        sourceTable,
        @sourceDatabase + '.' + @sourceSchema + '.' + sourceTable
    )  as [text()]
from (
    select
        metadata,
        query,
        sourceTable,
        major_id
    from
        sys.extended_properties
    cross apply (
        values (
            cast([value] as nvarchar(max))
        )
    ) d ([description])
    cross apply (
        values (
            left([description], patindex('%Query%', [description]) - 1),
            substring([description], patindex('%Query%', [description]) + 7, 100000000),
            substring(
                [description],
                patindex('%Table%', [description]) + 7,
                charindex(CHAR(10), [description], patindex('%Table%', [description]) + 7)
                - 1
                - (patindex('%Table%', [description]) + 7)
            )
        )
    ) e (metadata, query, sourceTable)
    where
        [description] like '%Query%'
) ep
join (
    select
        *,
        OBJECT_ID(name) as major_id
    from
        _Knot
) k
on
    ep.major_id = k.major_id
for xml path('')