Anchor Model Generator

/* -----------------------------------------------------------------------------------------------------------------------------------

    Script for automatically generating an Anchor model based on a normalized or dimensional model.

    VERSION HISTORY
    2016-11-15  Lars Rönnbäck         CREATED
    2016-11-17  Lars Rönnbäck         Merged features from the script made by Juan-José van der Linden.
                                        Added metadata and descriptions, improved ties.
    2016-11-29  Lars Rönnbäck         Added features suggested by Frederik Carlsson:
                                        + Existing stats that are not FULLSCAN are updated.
                                        + When determining knot identity a fill factor is respected.
                                        + Historization of all, no, or only knotted attributes.
                                        + Data length can be checked to avoid creating poor performing knots.
                                        + Knot loading code is generated in the knot descriptions.
                                        + Generate checksums if key length larger than 900 bytes.
                                        + Add support for anchorizing views
                                        + Made it possible to control validity tracking.
                                        Foreign keys can be used instead of column names to find relationships.
    2017-05-02  Lars Rönnbäck         Fix for other schemas than 'dbo'.
    2017-05-05  Lars Rönnbäck         Knot loading code now excludes null values.

    LIMITATIONS
    - Creates only binary ties.
    - Does not handle composed primary keys.
    - Skips self referencing ties.

-------------------------------------------------- VARIABLES CONTROLLING THE SCRIPT ----------------------------------------------- */

declare @tableName varchar(555) = null; -- null for all tables
declare @schemaName varchar(555) = 'dbo'; -- null for all schemas
declare @knotSize varchar(42) = 'smallint'; -- largest allowed data type for knots
declare @fillFactor float = 0.6; -- knots that become filled to more than 60% are switched to a larger datatype
declare @checkLengths bit = 1; -- controls if average data length should determine the existance of knots
declare @timeRange varchar(42) = 'datetime'; -- default time range for historized constructs
declare @historization char(1) = 'K'; -- historization options (K = knotted attributes, 0 = no attributes, * = all attributes)
declare @metadataUsage varchar(5) = 'true'; -- whether to use metadata or not (true/false)
declare @objectTypes varchar(555) = 'USER_TABLE'; -- this can be USER_TABLE and/or VIEW
declare @trackValidity char(1) = 'T'; -- validity tracking options (T = only for ties, 0 = never, * = ties and anchors)
declare @relationshipDetection char(1) = 'N'; -- options for finding relationships (F = foreign keys, N = column names, * = use both)

--------------------------------------------------------------------------------------------------------------------------------------

if OBJECT_ID('tempdb..#schema') is not null
drop table #schema;

with mnemonics as (
    select
        o.object_id,
        0 as column_id,
        cast(
            char(65 + (row_number() over (order by o.object_id) - 1) / 26) +
            char(65 + (row_number() over (order by o.object_id) - 1) % 26)
        as varchar(3)) as [mnemonic],
        (row_number() over (order by o.object_id) - 1) as startIndex,
        o.name,
        s.name as schemaName
    from -- select * from
        sys.objects o
    join
        sys.schemas s
    on
        s.schema_id = o.schema_id
    and
        s.name = isnull(@schemaName, s.name)
    where
        @objectTypes like '%' + o.type_desc + '%'
    union all
    select
        o.object_id,
        isnull(c.column_id, 0) as column_id,
        cast (
            left(o.mnemonic, 1) +
            char(65 + (o.startIndex + (row_number() over (order by o.object_id) - 1) / 26) % 26) +
            char(65 + (row_number() over (order by o.object_id, c.column_id) - 1) % 26)
        as varchar(3)) as [mnemonic],
        o.startIndex,
        c.name,
        o.schemaName
    from -- select * from
        mnemonics o
    join
        sys.columns c
    on
        c.object_id = o.object_id
    where
        o.column_id = 0
)
select
    object_id,
    column_id,
    mnemonic,
    name,
    schemaName,
    cast(null as float) as [density],
    cast(null as float) as [averageLength],
    cast(null as varchar(555)) as [dataType],
    cast(null as varchar(555)) as [dataRange],
    cast(null as int) as [maxLength]
into
    #schema
from
    mnemonics
where
    @tableName is null
or
    @tableName = object_name(object_id);

update s
set
    s.[dataType] = d.name,
    s.[dataRange] = d.name +
        -- select * from sys.columns where system_type_id = (select system_type_id from sys.types where name = 'time')
        case
            when d.name in ('varbinary', 'varchar', 'nvarchar', 'char', 'nchar')
            then '(' + cast(c.max_length as varchar(10)) + ')'
            when d.name in ('numeric', 'decimal')
            then '(' + cast(c.precision as varchar(10)) + ',' + cast(c.scale as varchar(10)) + ')'
            when d.name in ('time')
            then '(' + cast(c.precision as varchar(10)) + ')'
            else ''
        end,
    s.[maxLength] = c.max_length
from
    #schema s
join
    sys.columns c
on
    c.object_id = s.object_id
and
    c.column_id = s.column_id
join -- select * from
    sys.types d
on
    d.system_type_id = c.system_type_id

declare @object_id int;
declare @column_id int;
declare @mnemonic char(3);
declare @name varchar(555);

if OBJECT_ID('tempdb..#densities') is not null
drop table #densities;

create table #densities (
    [All density] float,
    [Average Length] float,
    [Columns] varchar(555)
);

if OBJECT_ID('tempdb..#header') is not null
drop table #header;

create table #header (
    [Name] varchar(555),
    [Updated] datetime,
    [Rows] bigint,
    [Rows Sampled] bigint,
    [Steps] int,
    [Density] float,
    [Average key length] float,
    [String Index] varchar(3),
    [Filter Expression] varchar(555),
    [Unfiltered Rows] bigint
);


declare column_cursor cursor for (
    select
        object_id,
        column_id,
        mnemonic,
        name,
        schemaName
    from
        #schema
    where
        column_id > 0
)

open column_cursor;
fetch next from column_cursor into @object_id, @column_id, @mnemonic, @name, @schemaName;

declare @sql varchar(max);
declare @stats varchar(555);

while @@FETCH_STATUS = 0
begin
    set @stats = null;

    select
        @stats = s.name
    from
        sys.stats_columns sc
    join
        sys.stats s
    on
        s.object_id = sc.object_id
    and
        s.stats_id = sc.stats_id
    where
        sc.object_id = @object_id
    and
        sc.column_id = @column_id;

    set @sql = 'dbcc show_statistics(''' + @schemaName + '.' + object_name(@object_id) + ''', ''' + @stats + ''') WITH STAT_HEADER';
    print @sql;

    truncate table #header;
    insert into #header exec(@sql);

    -- update existing stats that are outdated
    if exists (
        select top 1
            Name
        from
            #header
        where
            [Rows Sampled] <> [Rows]
    )
    begin
        set @sql = 'update statistics [' + @schemaName + '].[' + object_name(@object_id) + ']([' + @stats + ']) WITH FULLSCAN';
        print @sql;
        exec(@sql);
    end

    if @stats is null and not exists (
        select top 1
            name
        from
            sys.columns
        where
            object_id = @object_id
        and
            column_id = @column_id
        and
            -- these types cannot have statistics
            system_type_id in (
                241 -- xml
            )
    )
    begin
        set @stats = 's_' + @mnemonic + '_' + @name + '';
        set @sql = 'create statistics [' + @stats + '] on [' + @schemaName + '].[' + object_name(@object_id) + ']([' + @name + ']) WITH FULLSCAN';
        print @sql;
        exec(@sql);
    end

    set @sql = 'dbcc show_statistics(''' + @schemaName + '.' + object_name(@object_id) + ''', ''' + @stats + ''') WITH DENSITY_VECTOR';
    print @sql;

    truncate table #densities;
    insert into #densities exec(@sql);

    update #schema
    set
        density = (select top 1 [All density] from #densities where [Columns] = @name),
        averageLength = (select top 1 [Average Length] from #densities where [Columns] = @name)
    where
        object_id = @object_id
    and
        column_id = @column_id;

    fetch next from column_cursor into @object_id, @column_id, @mnemonic, @name, @schemaName;
end

close column_cursor;
deallocate column_cursor;

--- find primary keys
if OBJECT_ID('tempdb..#primaryKeyColumns') is not null
drop table #primaryKeyColumns;

select
    i.object_id,
    max(COL_NAME(i.OBJECT_ID, ic.column_id)) AS name
into
    #primaryKeyColumns
from
    sys.indexes i
join
    sys.index_columns ic
on
    ic.OBJECT_ID = i.OBJECT_ID
and
    ic.index_id = i.index_id
where
    i.is_primary_key = 1
group by
    i.object_id
having
    COUNT(*) = 1;

--- find foreign key relationships
if OBJECT_ID('tempdb..#foreignKeyRelationships') is not null
drop table #foreignKeyRelationships;

select distinct
    parent_object_id,
    referenced_object_id
into
    #foreignKeyRelationships
from
    sys.foreign_key_columns;

--- find foreign key columns
if OBJECT_ID('tempdb..#foreignKeyColumns') is not null
drop table #foreignKeyColumns;

select distinct
    parent_object_id,
    parent_column_id,
    referenced_object_id,
    referenced_column_id
into
    #foreignKeyColumns
from
    sys.foreign_key_columns;


-- XML generation
with knots as (
    select
        object_id,
        column_id,
        mnemonic,
        name,
        density,
        averageLength,
        dataType,
        dataRange,
        maxLength
    from
        #schema
    cross apply (
        values (
            case
                when @knotSize = 'tinyint'  then 1E0 / 255
                when @knotSize = 'smallint' then 1E0 / 32767
                when @knotSize = 'int'      then 1E0 / 2147483647
            end / @fillFactor,
            case
                when @knotSize = 'tinyint'  then 1
                when @knotSize = 'smallint' then 2
                when @knotSize = 'int'      then 4
            end
        )
    ) v (minDensity, minLength)
    where
        density > minDensity
    and
        averageLength > (@checkLengths * minLength)
    and
        dataType in ('varchar', 'nvarchar', 'char', 'nchar')
    union
    select
        -- create a validity knot that ties and anchors are connected to
        0, 0, 'VAL', 'Validity', 1, 42, 'varchar', 'varchar(42)', 42
    where
        @trackValidity in ('T', '*')
)
select
    '0.99' as [@format],
    convert(char(10), getdate(), 121) as [@date],
    convert(char(8), cast(getdate() as time(0)), 121) as [@time],
    SYSTEM_USER as [@creator],
    @timeRange as [metadata/@changingRange],
    @schemaName as [metadata/@encapsulation],
    'int' as [metadata/@identity],
    'SQLServer' as [metadata/@databaseTarget],
    'uni' as [metadata/@temporalization],
    @metadataUsage as [metadata/@metadataUsage],
    (   -- knots
        select
            k.mnemonic as [@mnemonic],
            case
                when k.density > 1E0 / 255        / @fillFactor then 'tinyint'
                when k.density > 1E0 / 32767      / @fillFactor then 'smallint'
                when k.density > 1E0 / 2147483647 / @fillFactor then 'int'
            end as [@identity],
            v.descriptor as [@descriptor],
            k.dataRange as [@dataRange],
            @schemaName as [metadata/@capsule],
            'true' as [metadata/@generator],
            case when k.maxLength > 900 then 'true' end as [metadata/@checksum],
            case
                when t.name is not null then
                'Density: ' + CAST(k.density as varchar(42)) + CHAR(10) +
                'Average length: ' + CAST(k.averageLength as varchar(42)) + CHAR(10) +
                'Column: ' + k.name + CHAR(10) +
                'Table: ' + t.name + CHAR(10) +
                'Query: ' +
                CHAR(10) +
                'MERGE [' + @schemaName + '].[' + k.mnemonic + '_' + v.descriptor + '] k ' + CHAR(10) +
                'USING ( ' + CHAR(10) +
                CHAR(9) + 'SELECT DISTINCT ' + CHAR(10) +
                CHAR(9) + CHAR(9) + c.name + CHAR(10) +
                CHAR(9) + 'FROM ' + CHAR(10) +
                CHAR(9) + CHAR(9) + t.name + CHAR(10) +
                CHAR(9) + 'WHERE ' + CHAR(10) +
                CHAR(9) + CHAR(9) + c.name + ' is not null ' + CHAR(10) +
                ') src ' + CHAR(10) +
                'ON ' + CHAR(10) +
                CHAR(9) + 'src.' + c.name + ' = k.[' + k.mnemonic + '_' + v.descriptor + '] ' + CHAR(10) +
                'WHEN NOT MATCHED THEN INSERT ( ' + CHAR(10) +
                CHAR(9) + '[' + k.mnemonic + '_' + v.descriptor + ']' +
                case when @metadataUsage = 'true' then ',' + CHAR(10) + CHAR(9) + '[Metadata_' + k.mnemonic + ']' else '' end + CHAR(10) +
                ') ' + CHAR(10) +
                'VALUES ( ' + CHAR(10) +
                CHAR(9) + 'src.' + c.name +
                case when @metadataUsage = 'true' then ',' + CHAR(10) + CHAR(9) + '0' else '' end + CHAR(10) +
                ');' + CHAR(10)
                else
                'Tracks existence over time. ' +
                'Automatically added to all schemas.'
            end as [description]
        from
            knots k
        cross apply (
            values (
                replace(replace(replace(replace(replace(replace(replace(replace(
                replace(replace(replace(replace(replace(replace(replace(
                replace(replace(replace(replace(replace(replace(replace(
                replace(replace(replace(replace(replace(
                    ' '+replace(k.name,'_',' '),
                ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
                ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
                ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
                ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
                ' y',' Y'),' z',' Z'),' ','')
            )
        ) v (descriptor)
        left join
            #schema t
        on
            t.object_id = k.object_id
        and
            t.column_id = 0
        left join
            #schema c
        on
            c.object_id = k.object_id
        and
            c.column_id = k.column_id
        left join
            #primaryKeyColumns pk
        on
            pk.name COLLATE DATABASE_DEFAULT = k.name COLLATE DATABASE_DEFAULT
        and
            pk.object_id <> k.object_id
        where
            pk.object_id is null
        for xml path('knot'), type
    ),
    (   -- anchors
        select
            t.mnemonic as [@mnemonic],
            vt.descriptor as [@descriptor],
            'int' as [@identity],
            @schemaName as [metadata/@capsule],
            'true' as [metadata/@generator],
            (   -- unknotted attributes
                select
                    c.mnemonic as [@mnemonic],
                    vc.descriptor as [@descriptor],
                    c.dataRange as [@dataRange],
                    case when @historization in ('*') then @timeRange else null end as [@timeRange],
                    @schemaName as [metadata/@capsule],
                    case when c.maxLength > 900 then 'true' end as [metadata/@checksum],
                    case when c.density is not null then 'Density: ' + CAST(c.density as varchar(42)) + CHAR(10) else '' end +
                    'Column: ' + c.name + CHAR(10) +
                    'Table: ' + t.name as [description]
                from
                    #schema c
                cross apply (
                    values (
                        replace(replace(replace(replace(replace(replace(replace(replace(
                        replace(replace(replace(replace(replace(replace(replace(
                        replace(replace(replace(replace(replace(replace(replace(
                        replace(replace(replace(replace(replace(
                            ' '+replace(c.name,'_',' '),
                        ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
                        ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
                        ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
                        ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
                        ' y',' Y'),' z',' Z'),' ','')
                    )
                ) vc (descriptor)
                left join
                    knots k
                on
                    k.object_id = c.object_id
                and
                    k.column_id = c.column_id
                left join
                    #primaryKeyColumns pk
                on
                    pk.name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
                and
                    pk.object_id <> c.object_id
                where
                    c.object_id = t.object_id
                and
                    c.column_id > 0
                and
                    k.object_id is null
                and
                    pk.object_id is null
                for xml path ('attribute'), type
            ),
            (   -- knotted attributes
                select
                    c.mnemonic as [@mnemonic],
                    replace(replace(replace(replace(replace(replace(replace(replace(
                    replace(replace(replace(replace(replace(replace(replace(
                    replace(replace(replace(replace(replace(replace(replace(
                    replace(replace(replace(replace(replace(
                        ' '+replace(c.name,'_',' '),
                    ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
                    ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
                    ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
                    ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
                    ' y',' Y'),' z',' Z'),' ','') as [@descriptor],
                    k.mnemonic as [@knotRange],
                    case when @historization in ('*', 'K') then @timeRange else null end as [@timeRange],
                    @schemaName as [metadata/@capsule],
                    'false' as [metadata/@restatable],
                    'true' as [metadata/@idempotent],
                    case when c.density is not null then 'Density: ' + CAST(c.density as varchar(42)) + CHAR(10) else '' end +
                    'Column: ' + c.name + CHAR(10) +
                    'Table: ' + t.name as [description]
                from
                    #schema c
                join
                    knots k
                on
                    k.object_id = c.object_id
                and
                    k.column_id = c.column_id
                left join
                    #primaryKeyColumns pk
                on
                    pk.name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
                and
                    pk.object_id <> c.object_id
                where
                    c.object_id = t.object_id
                and
                    c.column_id > 0
                and
                    pk.object_id is null
                for xml path ('attribute'), type
            ),
            (   -- validity attribute
                select
                    'VAL' as [@mnemonic],
                    'Validity' as [@descriptor],
                    'VAL' as [@knotRange],
                    @timeRange as [@timeRange],
                    @schemaName as [metadata/@capsule],
                    'false' as [metadata/@restatable],
                    'true' as [metadata/@idempotent],
                    'Tracks existence of that which the anchor represents. ' +
                    'Automatically added to all anchors by the generator.' as [description]
                where
                    @trackValidity = '*'
                for xml path ('attribute'), type
            ),
            'Table: ' + t.name as [description]
        from
            #schema t
        cross apply (
            values (
                replace(replace(replace(replace(replace(replace(replace(replace(
                replace(replace(replace(replace(replace(replace(replace(
                replace(replace(replace(replace(replace(replace(replace(
                replace(replace(replace(replace(replace(
                    ' '+replace(t.name,'_',' '),
                ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
                ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
                ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
                ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
                ' y',' Y'),' z',' Z'),' ','')
            )
        ) vt (descriptor)
        where
            t.column_id = 0
        for xml path ('anchor'), type
    ),
    case when @relationshipDetection in ('N', '*') then (
        -- ties
        select
            case when fk.referenced_object_id is null then @timeRange else null end as [@timeRange],
            @schemaName as [metadata/@capsule],
            case when fk.referenced_object_id is null then 'false' else null end as [metadata/@restatable],
            case when fk.referenced_object_id is null then 'true' else null end as [metadata/@idempotent],
            case when fk.referenced_object_id is null and @trackValidity in ('T', '*') then 'currently' else null end as [knotRole/@role],
            case when fk.referenced_object_id is null and @trackValidity in ('T', '*') then 'VAL' else null end as [knotRole/@type],
            case when fk.referenced_object_id is null and @trackValidity in ('T', '*') then 'false' else null end as [knotRole/@identifier],
            'n' + c.mnemonic + c.name +
            replace(replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(
                ' '+replace(t1.name,'_',' '),
            ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
            ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
            ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
            ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
            ' y',' Y'),' z',' Z'),' ','') as [anchorRole/@role],
            t1.mnemonic as [anchorRole/@type],
            'true' as [anchorRole/@identifier],
            null, -- this starts a new anchorRole
            'n' +
            replace(replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(
                ' '+replace(t2.name,'_',' '),
            ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
            ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
            ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
            ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
            ' y',' Y'),' z',' Z'),' ','') as [anchorRole/@role],
            t2.mnemonic as [anchorRole/@type],
            case when fk.referenced_object_id is null then 'true' else 'false' end as [anchorRole/@identifier],
            'Left table: ' + t1.name + CHAR(10) +
            'Relating column: ' + c.name + ' (' + c.mnemonic + ')' + CHAR(10) +
            'Right table: ' + t2.name as [description]
        from
            #schema t1
        join
            #schema c
        on
            c.object_id = t1.object_id
        and
            c.column_id > 0
        join
            #primaryKeyColumns pk
        on
            pk.name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
        join
            #schema t2
        on
            t2.object_id = pk.object_id
        and
            t2.column_id = 0
        and
            t2.object_id <> t1.object_id
        left join
            #foreignKeyRelationships fk
        on
            fk.parent_object_id = t1.object_id
        and
            fk.referenced_object_id = t2.object_id
        where
            t1.column_id = 0
        for xml path ('tie'), type
    ) end,
    case when @relationshipDetection in ('F', '*') then (
        -- ties
        select
            case when @trackValidity in ('T', '*') then @timeRange else null end as [@timeRange],
            @schemaName as [metadata/@capsule],
            case when @trackValidity in ('T', '*') then 'false' else null end as [metadata/@restatable],
            case when @trackValidity in ('T', '*') then 'true' else null end as [metadata/@idempotent],
            case when @trackValidity in ('T', '*') then 'currently' else null end as [knotRole/@role],
            case when @trackValidity in ('T', '*') then 'VAL' else null end as [knotRole/@type],
            case when @trackValidity in ('T', '*') then 'false' else null end as [knotRole/@identifier],
            'f' + c1.mnemonic + c1.name +
            replace(replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(
                ' '+replace(t1.name,'_',' '),
            ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
            ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
            ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
            ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
            ' y',' Y'),' z',' Z'),' ','') + 'Foreign' as [anchorRole/@role],
            t1.mnemonic as [anchorRole/@type],
            'true' as [anchorRole/@identifier],
            null, -- this starts a new anchorRole
            'f' +
            replace(replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(replace(replace(
            replace(replace(replace(replace(replace(
                ' '+replace(t2.name,'_',' '),
            ' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),
            ' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),
            ' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),
            ' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),
            ' y',' Y'),' z',' Z'),' ','') as [anchorRole/@role],
            t2.mnemonic as [anchorRole/@type],
            case when fk.referenced_object_id is null then 'true' else 'false' end as [anchorRole/@identifier],
            'Left table: ' + t1.name + CHAR(10) +
            'Foreign key column: ' + c1.name + ' (' + c1.mnemonic + ')' + CHAR(10) +
            'Right table: ' + t2.name + CHAR(10) +
            'Referenced column: ' + c2.name + ' (' + c2.mnemonic + ')' as [description]
        from
            #foreignKeyColumns fk
        join
            #schema c1
        on
            c1.object_id = fk.parent_object_id
        and
            c1.column_id = fk.parent_column_id
        join
            #schema t1
        on
            t1.object_id = fk.parent_object_id
        and
            t1.column_id = 0
        join
            #schema c2
        on
            c2.object_id = fk.referenced_object_id
        and
            c2.column_id = fk.referenced_column_id
        join
            #schema t2
        on
            t2.object_id = fk.referenced_object_id
        and
            t2.column_id = 0
        for xml path ('tie'), type
    ) end
for xml path ('schema')