if exists(select * from sys.objects where name = 'sp_Generate_SpreadTables' and [type] in ('P', 'PC')) 
    drop procedure sp_Generate_SpreadTables
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE sp_Generate_SpreadTables 
AS
BEGIN
    SET NOCOUNT ON;
    declare @sql nvarchar(2000), @table sysname, @InsSql nvarchar(2000), @type_ nvarchar(1000), @UpdType_ nvarchar(1000), @isNvarchar bit;
    set @table = N'';
    set @sql = N'';
    set @InsSql = N'';
    set @type_ = N'';
    set @UpdType_ = N'';
    select @isNvarchar = 
    (
        select case
                    when t.name = N'nvarchar' then 1
                    else 0
               end 
        from sys.columns c
            join sys.types t on t.system_type_id = c.system_type_id
                    and
                t.user_type_id = c.user_type_id
        where c.object_id = OBJECT_ID(N'VCLib.T_ENTITIES')
                and
            c.name = N'EntName'
    )

    declare @tsql nvarchar(max)
    if object_id(N'tempdb..#EntitiesHierarchy') is not null
        drop table #EntitiesHierarchy
    create table #EntitiesHierarchy(father int, son int)
    create clustered index ind_#EntitiesHierarchy_Father on #EntitiesHierarchy(father)
    ;with CTE as 
        (
            SELECT EntityId, BaseEntity, 0 as Lvl
            FROM VCLib.T_ENTITIES
            WHERE BaseEntity is null
            UNION All
            SELECT e.EntityId, e.BaseEntity, CTE.Lvl + 1
            FROM VCLib.T_ENTITIES as e
            JOIN CTE
            ON e.BaseEntity = CTE.EntityId
        )
    select* into #preTree from CTE

    create table #Num(c1 int)
    declare @lvl int, @tsqlFrom nvarchar(max), @tsqlLJoin nvarchar(max), @tsqlSelect nvarchar(max), @tsqlSelect1 nvarchar(max), @tsqlOrder nvarchar(max), @tsqlInto nvarchar(30)
    declare @from nvarchar(max), @tsql2 nvarchar(max), @num1 nvarchar(max), @num2 nvarchar(max)
    select @from = N'', @tsql2 = N'', @num1 = N'', @num2 = N''
    set @tsqlInto = N'
    into #tree'
    set @tsqlSelect = N'select distinct
        t0.EntityId as l0'
    set @tsql = N''
    set @tsqlLJoin = N''
    set @tsqlFrom = N'
    from #preTree t0'
    set @tsqlOrder = N''
    set @tsqlSelect1 = N''
    DECLARE cTree CURSOR FOR 
    SELECT Lvl FROM #preTree
    group by Lvl
    order by Lvl;

    OPEN cTree

    FETCH NEXT FROM cTree 
    INTO @lvl

    WHILE @@FETCH_STATUS = 0
    BEGIN
        if @lvl = 0
        begin        
            set @tsqlSelect1 = N't0.EntityId'
        end
        else
        begin
            if @lvl = 1
                set @tsqlOrder = N'
    order by 1'
            set @tsqlLJoin = @tsqlLJoin + N'
    left join #preTree t' + cast(@lvl as nvarchar(5)) + N' on t' + cast(@lvl -1 as nvarchar(5)) + N'.EntityId = t' + cast(@lvl as nvarchar(5)) + N'.BaseEntity and t' + cast(@lvl - 1 as nvarchar(5)) + N'.lvl = ' + cast(@lvl -1 as nvarchar(5))
            set @tsqlSelect1 = N't' + cast(@lvl as nvarchar(5)) + N'.EntityId,' + @tsqlSelect1;
            set @tsqlSelect = @tsqlSelect + N'
    ,    coalesce(' + @tsqlSelect1 + N') as l' + cast(@lvl as nvarchar(5))
            if @lvl > 1
                set @tsqlOrder = @tsqlOrder + N',' + cast(@lvl as nvarchar(5))
        end
        insert into #Num(c1)
        Values(@lvl)
        FETCH NEXT FROM cTree 
        INTO @lvl
    END 
    CLOSE cTree;
    DEALLOCATE cTree;

    select @from = @from + 
    N'
    select distinct l' + cast(n1.c1 as nvarchar(5)) + N' as father, l' + cast(n2.c1 as nvarchar(5)) + N' as son
    from #tree
    union'
    from #Num n1
    cross join
        #Num n2
    where n1.c1 <= n2.c1
    order by n1.c1, n2.c1 desc

    set @from = left(@from,len(@from)-6)

    set @tsql2 = N'
    select distinct father, son
    from
    (' + @from + N'
    ) Tab
    '
    set @tsql = @tsqlSelect + @tsqlInto + @tsqlFrom + @tsqlLJoin + @tsqlOrder + @tsql2 
    --print @tsql
    insert into #EntitiesHierarchy(father, son)
    exec(@tsql)

    select b.father, b.son, e0.[Schema] + N'.' + e0.EntName as BaseEntity, e1.[Schema] + N'.' + e1.EntName as Entity
    into #BaseEntity
    from #EntitiesHierarchy b
    inner join
        #preTree t
        on t.EntityId = b.son
    inner join
        VCLib.T_ENTITIES e0
        on e0.EntityId = b.father
    inner join
        VCLib.T_ENTITIES e1
        on e1.EntityId = b.son
    where e0.BaseEntity is null;



-- Spread - атрибуты 
    DECLARE c CURSOR
        LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR    
        select e.[Schema] + N'.F_' + e.EntName + N'_' + ea.Name as tabname,
        N'create table [' 
            + e.[Schema] + N'].[F_' 
            + e.EntName + N'_' 
            + ea.Name + N'](' 
            + N'ID '
            + case 
                    when t_pk.name is null then case
                                                    when ea.SystemType in (N'int', N'bigint') then ea.SystemType
                                                    when ea.SystemType = N'guid' then N'uniqueidentifier'
                                                    when @isNvarchar = 0 and ea.SystemType = N'string' then N'varchar(' + cast(ea.[MaxLength] as nvarchar(5)) + N')'
                                                    when @isNvarchar = 1 and ea.SystemType = N'string' then N'nvarchar(' + cast(ea.[MaxLength] as nvarchar(5)) + N')'
                                                end
                    else t_pk.name + case when t_pk.system_type_id in (106, 108) -- decimal, numeric
                                              then N'(' + cast(col_pk.[precision] as nvarchar(10)) + N', ' + cast(col_pk.scale as nvarchar(10)) + N')'
                                          when t_pk.system_type_id in (165, 167, 175, 231, 239) -- varbinary, varchar, char, nvarchar, nchar
                                              then N'(' + case when col_pk.max_length = -1 then N'max' else cast(col_pk.max_length as nvarchar(20)) end  + N')'
                                          else N''
                                     end
              end
            + case when ea.IsPrimaryKey = 1 then N'' else N', [value] ' end
            + case when ea.IsPrimaryKey = 1 then N''
                    when ea.AttributeType = 4 -- Choice
                    then N'int'
                    when ea.AttributeType = 0 -- Attribute
                    then case when ea.SystemType in (N'int', N'bigint', N'datetime')
                              then ea.SystemType
                              when ea.SystemType = N'guid'
                              then N'uniqueidentifier'
                              when @isNvarchar = 0 and ea.SystemType = N'text' or (ea.SystemType = N'string' and ea.[MaxLength] is null)
                              then N'varchar(max)'
                              when @isNvarchar = 1 and ea.SystemType = N'text' or (ea.SystemType = N'string' and ea.[MaxLength] is null)
                              then N'nvarchar(max)'
                              when ea.SystemType in (N'binary', N'byte')
                              then N'varbinary(' + isnull(cast(ea.[MaxLength] as nvarchar(5)),N'max') +N')'
                              when ea.SystemType in (N'bool', N'boolean')
                              then N'bit'
                              when ea.SystemType = N'float'
                              then N'decimal(' + cast(ea.[Precision] as nvarchar(2)) + N', ' + cast(ea.[Scale] as nvarchar(2)) + N')'
                              when ea.SystemType = N'date'
                              then N'datetime'
                              when @isNvarchar = 0 and ea.SystemType = N'string'
                              then N'varchar(' + isnull(cast(ea.[MaxLength] as nvarchar(5)),N'max') + N')'
                              when @isNvarchar = 1 and ea.SystemType = N'string'
                              then N'nvarchar(' + isnull(cast(ea.[MaxLength] as nvarchar(5)),N'max') + N')'
                            end
                    -- Parent, Link, Child
                    else case when eaBase.SystemType in (N'int', N'bigint', N'datetime')
                              then eaBase.SystemType
                              when eaBase.SystemType = N'guid'
                              then N'uniqueidentifier'
                              when @isNvarchar = 0 and eaBase.SystemType = N'text' or (eaBase.SystemType = N'string' and eaBase.[MaxLength] is null)
                              then N'varchar(max)'
                              when @isNvarchar = 1 and eaBase.SystemType = N'text' or (eaBase.SystemType = N'string' and eaBase.[MaxLength] is null)
                              then N'nvarchar(max)'
                              when eaBase.SystemType in (N'binary', N'byte')
                              then N'varbinary(' + isnull(cast(eaBase.[MaxLength] as nvarchar(5)),N'max') +N')'
                              when eaBase.SystemType in (N'bool', N'boolean')
                              then N'bit'
                              when eaBase.SystemType = N'float'
                              then N'decimal(' + cast(eaBase.[Precision] as nvarchar(2)) + N', ' + cast(eaBase.[Scale] as nvarchar(2)) + N')'
                              when eaBase.SystemType = N'date'
                              then N'datetime'
                              when @isNvarchar = 0 and eaBase.SystemType = N'string'
                              then N'varchar(' + isnull(cast(eaBase.[MaxLength] as nvarchar(5)),N'max') + N')'
                              when @isNvarchar = 1 and eaBase.SystemType = N'string'
                              then N'nvarchar(' + isnull(cast(eaBase.[MaxLength] as nvarchar(5)),N'max') + N')'
                            end
                end
            + case when ea.IsRequired = 1 then N' not null' else N'' end
            + N', CONSTRAINT [PK_F_' + e.EntName + N'_' + ea.Name + N'] PRIMARY KEY CLUSTERED(ID)'
            + N');'
            + case
                   when ea.AttributeType = 1 and eaBase.MappingType != 6 then 
                        N'alter table [' 
                            + e.[Schema] + N'].[F_' 
                            + e.EntName + N'_' 
                            + ea.Name + N'] ADD CONSTRAINT [FK_SPREAD_' 
                            + REPLACE(ea.SystemType, N'.', N'_') + N'_' 
                            + ea.Name + N'] FOREIGN KEY([value]) REFERENCES [' 
                            + eBase.[Schema] + N'].['
                            + eaBase.MappingTable + N'] ([' 
                            + eaBase.MappingName + N']);'
                   else ''
              end
        from 
        (
            SELECT tea.[EntityID], [IsRequired], MappingType, [MappingName], [IsStored], [MappingTable], tea.Name, ISNULL(tea.SystemType, N'string') SystemType,
                case
                    when tea.[MaxLength] is null then 255
                    when @isNvarchar = 0 and tea.[MaxLength] > 8000 then null
                    when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                    else tea.[MaxLength]
                end MaxLength, ISNULL(tea.[Precision], 31) [Precision], ISNULL(tea.Scale, 10) Scale, tea.IsPrimaryKey, tea.AttributeType
            FROM VCLib.[T_ENTITIES_ATTRIBUTES] tea 
            where tea.MappingType = 5
            and tea.Name not like N'% %'
            UNION
            SELECT tea.[EntityID], [IsRequired], MappingType, [MappingName], [IsStored], [MappingTable], tea.Name, ISNULL(tea.SystemType, N'string') SystemType,
                case
                    when tea.[MaxLength] is null then 255
                    when @isNvarchar = 0 and tea.[MaxLength] > 8000 then null
                    when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                    else tea.[MaxLength]
                end MaxLength, ISNULL(tea.[Precision], 31) [Precision], ISNULL(tea.Scale, 10) Scale, tea.IsPrimaryKey, tea.AttributeType
            FROM [V_ENTITY_ATTRIBUTES] tea 
            where tea.MappingType = 5
            and tea.Name not like N'% %'
        ) ea
        inner join
            vclib.T_ENTITIES e
            on ea.EntityID = e.EntityID
        outer apply
        (
            select top 1 c.EntityID as ChildEntityID from vclib.T_ENTITIES c where c.BaseEntity = e.EntityID
        ) child
        inner join
            #BaseEntity be
            on be.son = e.EntityID
        inner join
        (
            SELECT tea.[EntityID], [IsRequired], MappingType, [MappingName], [IsStored], [MappingTable], tea.Name, ISNULL(tea.SystemType, N'string') SystemType
            FROM VCLib.[T_ENTITIES_ATTRIBUTES] tea 
            where tea.IsPrimaryKey = 1
            UNION
            SELECT tea.[EntityID], [IsRequired], MappingType, [MappingName], [IsStored], [MappingTable], tea.Name, ISNULL(tea.SystemType, N'string') SystemType
            FROM [V_ENTITY_ATTRIBUTES] tea 
            where tea.IsPrimaryKey = 1
        ) Base_ea
            on Base_ea.EntityID = be.Father
        inner join
            vclib.T_ENTITIES eBase
            on eBase.EntityID = be.father
        left join
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            on tc.TABLE_SCHEMA = eBase.[Schema]
            and tc.TABLE_NAME = Base_ea.MappingTable
            and tc.CONSTRAINT_TYPE = N'PRIMARY KEY'
        left JOIN
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
            ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
            AND KU.TABLE_SCHEMA = eBase.[Schema]
            AND KU.TABLE_NAME = Base_ea.MappingTable
        left join
            sys.columns col_pk
            on col_pk.object_id = object_id(eBase.[Schema] + N'.' + Base_ea.MappingTable)
            and col_pk.name = KU.COLUMN_NAME
        left join
            sys.types t_pk
            on t_pk.system_type_id = col_pk.system_type_id
        left join
            #BaseEntity valueBase
            on valueBase.Entity = ea.SystemType
        left join
        (
            SELECT tea.[EntityID], [IsRequired], MappingType, [MappingName], [IsStored], [MappingTable], tea.Name, ISNULL(tea.SystemType, N'string') SystemType,
                case
                    when tea.[MaxLength] is null then 255
                    when @isNvarchar = 0 and tea.[MaxLength] > 8000 then null
                    when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                    else tea.[MaxLength]
                end MaxLength, ISNULL(tea.[Precision], 31) [Precision], ISNULL(tea.Scale, 10) Scale
            FROM VCLib.[T_ENTITIES_ATTRIBUTES] tea 
            where tea.IsPrimaryKey = 1
            UNION
            SELECT tea.[EntityID], [IsRequired], MappingType, [MappingName], [IsStored], [MappingTable], tea.Name, ISNULL(tea.SystemType, N'string') SystemType,
                case
                    when tea.[MaxLength] is null then 255
                    when @isNvarchar = 0 and tea.[MaxLength] > 8000 then null
                    when @isNvarchar = 1 and tea.[MaxLength] > 4000 then null
                    else tea.[MaxLength]
                end MaxLength, ISNULL(tea.[Precision], 31) [Precision], ISNULL(tea.Scale, 10) Scale
            FROM [V_ENTITY_ATTRIBUTES] tea 
            where tea.IsPrimaryKey = 1
        ) eaBase
            on eaBase.EntityID = valueBase.father
    OPEN c;
    FETCH c INTO @table, @sql;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        if OBJECT_ID(@table, N'U') is null
        begin
            --print @sql
            exec sp_executesql @sql;
        end
    FETCH c INTO @table, @sql;
    END
    CLOSE c;
    DEALLOCATE c;



-- create type_ if needed
    DECLARE c CURSOR
        LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR    
        select
            N'Alter Table ' + tc.TABLE_SCHEMA + N'.[' + tc.TABLE_NAME + N'] Add type_ int; ',
            N'update cur set cur.type_ = ' + cast(pk.EntityID as nvarchar(10)) + N' from ' + tc.TABLE_SCHEMA + N'.[' + tc.TABLE_NAME + N'] cur'
        from 
        (
            SELECT ea.[EntityID], e.[Schema],
                case
                    when ea.MappingType = 5 then N'F_' + e.EntName + N'_' + ea.Name
                    else ea.MappingTable
                end MappingTable
            FROM VCLib.[T_ENTITIES_ATTRIBUTES] ea 
            join VCLib.T_ENTITIES e
                on e.EntityID = ea.EntityID
            where ea.IsPrimaryKey = 1
            UNION
            SELECT ea.[EntityID], e.[Schema],
                case
                    when ea.MappingType = 5 then N'F_' + e.EntName + N'_' + ea.Name
                    else ea.MappingTable
                end MappingTable
            FROM [V_ENTITY_ATTRIBUTES] ea             
            join VCLib.T_ENTITIES e
                on e.EntityID = ea.EntityID
            where ea.IsPrimaryKey = 1
        ) pk
        outer apply
        (
            select top 1 c.EntityID as ChildEntityID from vclib.T_ENTITIES c where c.BaseEntity = pk.EntityID
        ) child
        left join
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            on tc.TABLE_SCHEMA = pk.[Schema]
            and tc.TABLE_NAME = pk.MappingTable
            and tc.CONSTRAINT_TYPE = N'PRIMARY KEY'
        left JOIN
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
            ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
            AND KU.TABLE_SCHEMA = pk.[Schema]
            AND KU.TABLE_NAME = pk.MappingTable
        left join
            sys.columns col_pk
            on col_pk.object_id = object_id(pk.[Schema] + N'.' + pk.MappingTable)
            and col_pk.name = KU.COLUMN_NAME
        where 
        child.ChildEntityID is not null 
        and not exists (select * from sys.columns cType where cType.object_id = col_pk.object_id and cType.name = N'type_')
    OPEN c;
    --FETCH c INTO @table, @type_, @UpdType_, @sql;
    FETCH c INTO @type_, @UpdType_;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        if @type_ <> N'' and not exists(select * from sys.columns c where c.object_id = OBJECT_ID(@table, N'U') and name = N'type_')
        begin
            --print @type_
            exec(@type_);
            if @UpdType_ <> N''
            begin
                --print @UpdType_
                exec(@UpdType_)
            end
        end
    FETCH c INTO @type_, @UpdType_;
    END
    CLOSE c;
    DEALLOCATE c;

END
