CREATE PROCEDURE dbo.copy_user_objects @src_db_id AS INT
    , @dst_db_id AS INT
AS
DECLARE @tables TABLE (
    --table variable storing source table ids mapped to dest tables
    src_table_id INT
    , dst_table_id INT
    );
DECLARE @modules TABLE (
    --table variable storing source module ids mapped to dest modules
    src_module_id INT
    , title NVARCHAR(250)
    , dst_module_id INT
    );
DECLARE @enumerator TABLE (id INT);--enumerator for looping through relations and constraints
DECLARE @new_id INT --moved object's id in dest
    , @old_id INT;--moved object's id in src

BEGIN
    --clear previous user object_type
    --clear erd_links
    DELETE
    FROM [dbo].[erd_links]
    WHERE module_id IN (
            SELECT module_id
            FROM dbo.[modules]
            WHERE database_id = @dst_db_id
            );

    --clear erd_node_columns
    DELETE
    FROM [dbo].[erd_nodes_columns]
    WHERE module_id IN (
            SELECT module_id
            FROM dbo.[modules]
            WHERE database_id = @dst_db_id
            );

    --clear erd_nodes
    DELETE
    FROM [dbo].[erd_nodes]
    WHERE module_id IN (
            SELECT module_id
            FROM dbo.[modules]
            WHERE database_id = @dst_db_id
            );

    --clear tables_modules
    DELETE
    FROM [dbo].[tables_modules]
    WHERE module_id IN (
            SELECT module_id
            FROM dbo.[modules]
            WHERE database_id = @dst_db_id
            );

    --clear procedures_modules
    DELETE
    FROM [dbo].[procedures_modules]
    WHERE module_id IN (
            SELECT module_id
            FROM dbo.[modules]
            WHERE database_id = @dst_db_id
            );

    --clear modules
    DELETE
    FROM [dbo].[modules]
    WHERE database_id = @dst_db_id;

    --clear user-def uk columns
    DELETE
    FROM [dbo].[unique_constraints_columns]
    WHERE unique_constraint_id IN (
            SELECT [unique_constraint_id]
            FROM [dbo].[unique_constraints] uc
            INNER JOIN dbo.[tables] tab
                ON uc.table_id = tab.table_id
            WHERE uc.[source] = 'user'
                AND tab.database_id = @dst_db_id
            );

    --clear user-def uks
    DELETE
    FROM [dbo].[unique_constraints]
    WHERE [source] = 'user'
        AND table_id IN (
            SELECT table_id
            FROM [dbo].[tables]
            WHERE database_id = @dst_db_id
            );

    --clear user-def relation columns
    DELETE
    FROM [dbo].[tables_relations_columns]
    WHERE table_relation_id IN (
            SELECT [table_relation_id]
            FROM [dbo].[tables_relations] rel
            INNER JOIN dbo.[tables] tab
                ON rel.pk_table_id = tab.table_id
                    OR rel.fk_table_id = tab.table_id
            WHERE rel.[source] = 'user'
                AND tab.database_id = @dst_db_id
            );

    --clear user-def relations
    DELETE
    FROM [dbo].[tables_relations]
    WHERE [source] = 'user'
        AND (
            pk_table_id IN (
                SELECT table_id
                FROM [dbo].[tables]
                WHERE database_id = @dst_db_id
                )
            OR fk_table_id IN (
                SELECT table_id
                FROM [dbo].[tables]
                WHERE database_id = @dst_db_id
                )
            );

    --find matching tables in src and dst
    INSERT INTO @tables (
        src_table_id
        , dst_table_id
        )
    SELECT src_tables.table_id
        , dst_tables.table_id
    FROM dbo.[tables] src_tables
    INNER JOIN dbo.[tables] dst_tables
        ON src_tables.[schema] = dst_tables.[schema]
            AND src_tables.[name] = dst_tables.[name]
            AND src_tables.[object_type] = dst_tables.[object_type]
    WHERE src_tables.database_id = @src_db_id
        AND dst_tables.database_id = @dst_db_id;

    --find modules to add to dst
    INSERT INTO @modules (
        src_module_id
        , title
        )
    SELECT src.module_id
        , src.title
    FROM [dbo].[modules] src
    WHERE database_id = @src_db_id;

    --create modules
    INSERT INTO [dbo].[modules] (
        [database_id]
        , [title]
        , [description]
        , [description_search]
        , [erd_link_style]
        , [erd_show_types]
        , [ordinal_position]
        )
    SELECT @dst_db_id
        , src_modules.[title]
        , [description]
        , [description_search]
        , [erd_link_style]
        , [erd_show_types]
        , [ordinal_position]
    FROM [dbo].[modules] src_modules
    INNER JOIN @modules mods
        ON src_modules.module_id = mods.src_module_id;

    --find new modules' ids in dst
    UPDATE mods
    SET dst_module_id = dst.module_id
    FROM @modules mods
    INNER JOIN dbo.[modules] dst
        ON mods.title = dst.title
    WHERE dst.database_id = @dst_db_id;

    --add to modules: tables/views
    INSERT INTO tables_modules (
        [table_id]
        , [module_id]
        )
    SELECT dst_tables.[dst_table_id]
        , mods.[dst_module_id]
    FROM dbo.[tables_modules] src_table_modules
    INNER JOIN @modules mods
        ON mods.src_module_id = src_table_modules.module_id
    INNER JOIN dbo.[tables] src_tables
        ON src_table_modules.table_id = src_tables.table_id
    INNER JOIN @tables dst_tables
        ON src_tables.table_id = dst_tables.src_table_id
    WHERE src_tables.[database_id] = @src_db_id;

    --add to modules:procedures/functions
    INSERT INTO procedures_modules (
        [procedure_id]
        , [module_id]
        )
    SELECT dst_procedures.[procedure_id]
        , mods.[dst_module_id]
    FROM dbo.[procedures_modules] src_procedure_modules
    INNER JOIN @modules mods
        ON mods.src_module_id = src_procedure_modules.module_id
    INNER JOIN [dbo].[procedures] src_procedures
        ON src_procedure_modules.procedure_id = src_procedures.procedure_id
    INNER JOIN [dbo].[procedures] dst_procedures
        ON src_procedures.[schema] = dst_procedures.[schema]
            AND src_procedures.[name] = dst_procedures.[name]
            AND src_procedures.[object_type] = dst_procedures.[object_type]
    WHERE src_procedures.[database_id] = @src_db_id
        AND dst_procedures.[database_id] = @dst_db_id;

    --find relations to add
    INSERT INTO @enumerator (id)
    SELECT src_relations.table_relation_id relation_id
    FROM [dbo].[tables_relations] src_relations
    INNER JOIN [dbo].[tables] src_pk_tables
        ON src_relations.pk_table_id = src_pk_tables.table_id
    INNER JOIN [dbo].[tables] src_fk_tables
        ON src_relations.fk_table_id = src_fk_tables.table_id
    WHERE src_relations.[source] = 'user'
        AND src_pk_tables.database_id = @src_db_id
        AND src_fk_tables.database_id = @src_db_id
        AND src_pk_tables.table_id IN (
            SELECT src_table_id
            FROM @tables
            )
        AND src_fk_tables.table_id IN (
            SELECT src_table_id
            FROM @tables
            );

    --loop through relations to add
    WHILE EXISTS (
            SELECT 1
            FROM @enumerator
            )
    BEGIN
        SELECT TOP 1 @old_id = id
        FROM @enumerator

        --insert relation
        INSERT INTO [dbo].[tables_relations] (
            [pk_table_id]
            , [fk_table_id]
            , [source]
            , [name]
            , [description]
            , [status]
            , [update_rule]
            , [delete_rule]
            , [disabled]
            , [title]
            )
        SELECT dst_pk_tables.[dst_table_id]
            , dst_fk_tables.[dst_table_id]
            , src_relations.[source]
            , src_relations.[name]
            , src_relations.[description]
            , src_relations.[status]
            , src_relations.[update_rule]
            , src_relations.[delete_rule]
            , src_relations.[disabled]
            , src_relations.[title]
        FROM [dbo].[tables_relations] src_relations
        INNER JOIN [dbo].[tables] src_pk_tables
            ON src_relations.pk_table_id = src_pk_tables.table_id
        INNER JOIN [dbo].[tables] src_fk_tables
            ON src_relations.fk_table_id = src_fk_tables.table_id
        INNER JOIN @tables dst_pk_tables
            ON src_pk_tables.table_id = dst_pk_tables.src_table_id
        INNER JOIN @tables dst_fk_tables
            ON src_fk_tables.table_id = dst_fk_tables.src_table_id
        WHERE src_relations.table_relation_id = @old_id
            AND src_pk_tables.database_id = @src_db_id
            AND src_fk_tables.database_id = @src_db_id;

        --get copied relation's @new_id
        SET @new_id = scope_identity();

        --insert relation columns
        INSERT INTO [dbo].[tables_relations_columns] (
            [table_relation_id]
            , [column_pk_id]
            , [column_fk_id]
            , [ordinal_position]
            , [status]
            )
        SELECT @new_id relation_id
            , dst_pk_columns.[column_id] pk_column_id
            , dst_fk_columns.[column_id] fk_column_id
            , src_relations_columns.[ordinal_position]
            , src_relations_columns.[status]
        FROM [dbo].[tables_relations_columns] src_relations_columns
        INNER JOIN [dbo].[tables_relations] src_relations
            ON src_relations_columns.table_relation_id = src_relations.table_relation_id
        INNER JOIN [dbo].[tables] src_pk_tables
            ON src_relations.pk_table_id = src_pk_tables.table_id
        INNER JOIN [dbo].[tables] src_fk_tables
            ON src_relations.fk_table_id = src_fk_tables.table_id
        INNER JOIN @tables dst_pk_tables
            ON src_pk_tables.table_id = dst_pk_tables.src_table_id
        INNER JOIN @tables dst_fk_tables
            ON src_fk_tables.table_id = dst_fk_tables.src_table_id
        INNER JOIN dbo.[columns] src_fk_columns
            ON src_fk_columns.column_id = src_relations_columns.[column_fk_id]
        INNER JOIN dbo.[columns] src_pk_columns
            ON src_pk_columns.column_id = src_relations_columns.[column_pk_id]
        INNER JOIN dbo.[columns] dst_fk_columns
            ON src_fk_columns.[name] = dst_fk_columns.[name]
                AND dst_fk_tables.dst_table_id = dst_fk_columns.table_id
        INNER JOIN dbo.[columns] dst_pk_columns
            ON src_pk_columns.[name] = dst_pk_columns.[name]
                AND dst_pk_tables.dst_table_id = dst_pk_columns.table_id
        WHERE src_relations.table_relation_id = @old_id
            AND src_relations.[source] = 'user'
            AND src_pk_tables.database_id = @src_db_id
            AND src_fk_tables.database_id = @src_db_id;

        DELETE
        FROM @enumerator
        WHERE id = @old_id;
    END;

    --find constraints to add
    INSERT INTO @enumerator (id)
    SELECT [unique_constraint_id]
    FROM [dbo].[unique_constraints] src_constraints
    INNER JOIN dbo.[tables] src_tables
        ON src_constraints.table_id = src_tables.table_id
    INNER JOIN @tables dst_tables
        ON src_tables.table_id = dst_tables.src_table_id
    WHERE src_constraints.source = 'user'
        AND src_tables.database_id = @src_db_id;

    --loop through constraints to add
    WHILE EXISTS (
            SELECT 1
            FROM @enumerator
            )
    BEGIN
        SELECT TOP 1 @old_id = id
        FROM @enumerator

        --add constraint
        INSERT INTO [dbo].[unique_constraints] (
            [table_id]
            , [source]
            , [name]
            , [description]
            , [primary_key]
            , [status]
            )
        SELECT dst_tables.dst_table_id
            , src_constraints.[source]
            , src_constraints.[name]
            , src_constraints.[description]
            , src_constraints.[primary_key]
            , src_constraints.[status]
        FROM [dbo].[unique_constraints] src_constraints
        INNER JOIN dbo.[tables] src_tables
            ON src_constraints.table_id = src_tables.table_id
        INNER JOIN @tables dst_tables
            ON src_tables.table_id = dst_tables.src_table_id
        WHERE src_constraints.unique_constraint_id = @old_id
            AND src_constraints.source = 'user'
            AND src_tables.database_id = @src_db_id;

        --get copied constraint's @new_id
        SET @new_id = scope_identity();

        --add constraint columns
        INSERT INTO [dbo].[unique_constraints_columns] (
            [unique_constraint_id]
            , [column_id]
            , [ordinal_position]
            )
        SELECT @new_id
            , dst_columns.[column_id]
            , [src_constraint_columns].ordinal_position
        FROM [dbo].[unique_constraints_columns] src_constraint_columns
        INNER JOIN [dbo].[columns] src_columns
            ON src_columns.column_id = src_constraint_columns.column_id
        INNER JOIN dbo.[tables] src_tables
            ON src_columns.table_id = src_tables.table_id
        INNER JOIN @tables dst_tables
            ON src_tables.table_id = dst_tables.src_table_id
        INNER JOIN [dbo].[columns] dst_columns
            ON dst_columns.[name] = src_columns.[name]
                AND dst_columns.table_id = dst_tables.dst_table_id
        WHERE src_constraint_columns.unique_constraint_id = @old_id
            AND src_tables.database_id = @src_db_id;

        DELETE
        FROM @enumerator
        WHERE id = @old_id;
    END;

    --add erd nodes
    INSERT INTO [dbo].[erd_nodes] (
        [module_id]
        , [table_id]
        , [pos_x]
        , [pos_y]
        , [color]
        , [width]
        , [height]
        )
    SELECT dst_modules.[dst_module_id]
        , dst_tables.[dst_table_id]
        , src_nodes.[pos_x]
        , src_nodes.[pos_y]
        , src_nodes.[color]
        , src_nodes.[width]
        , src_nodes.[height]
    FROM [dbo].[erd_nodes] src_nodes
    INNER JOIN @modules dst_modules
        ON dst_modules.src_module_id = src_nodes.[module_id]
    INNER JOIN @tables dst_tables
        ON src_nodes.table_id = dst_tables.src_table_id;

    --erd node columns
    INSERT INTO [dbo].[erd_nodes_columns] (
        [module_id]
        , [node_id]
        , [column_id]
        )
    SELECT dst_modules.[dst_module_id]
        , dst_nodes.[node_id]
        , dst_columns.[column_id]
    FROM [dbo].[erd_nodes_columns] src_node_columns
    INNER JOIN @modules dst_modules
        ON dst_modules.src_module_id = src_node_columns.module_id
    INNER JOIN dbo.[columns] src_columns
        ON src_node_columns.column_id = src_columns.column_id
    INNER JOIN @tables dst_tables
        ON src_columns.table_id = dst_tables.src_table_id
    INNER JOIN dbo.[columns] dst_columns
        ON dst_columns.table_id = dst_tables.dst_table_id
            AND src_columns.[name] = dst_columns.[name]
    INNER JOIN [dbo].[erd_nodes] dst_nodes
        ON dst_nodes.[module_id] = dst_modules.dst_module_id
            AND dst_nodes.[table_id] = dst_tables.dst_table_id;

    --erd links
    WITH cte_relations
    AS (
        SELECT relation_columns.table_relation_id
            , relation_columns.ordinal_position
            , relations.[description]
            , pk_tables.[database_id] pkdbid
            , fk_tables.[database_id] fkdbid
            , CONCAT (
                pk_tables.[object_type]
                , '.'
                , pk_tables.[schema]
                , '.'
                , pk_tables.[name]
                , '.'
                , pk_columns.[name]
                , '='
                , fk_tables.[object_type]
                , '.'
                , fk_tables.[schema]
                , '.'
                , fk_tables.[name]
                , '.'
                , fk_columns.[name]
                , ';'
                ) keycol
        FROM [dbo].tables_relations relations
        INNER JOIN [dbo].tables_relations_columns relation_columns
            ON relations.table_relation_id = relation_columns.table_relation_id
        INNER JOIN [dbo].[columns] fk_columns
            ON relation_columns.column_fk_id = fk_columns.column_id
        INNER JOIN [dbo].[columns] pk_columns
            ON relation_columns.column_pk_id = pk_columns.column_id
        INNER JOIN [dbo].[tables] fk_tables
            ON fk_tables.table_id = relations.fk_table_id
        INNER JOIN [dbo].[tables] pk_tables
            ON pk_tables.table_id = relations.pk_table_id
        )
        , cte_keycols
    AS (
        SELECT pkdbid
            , fkdbid
            , table_relation_id
            , (
                SELECT col.keycol AS [text()]
                FROM cte_relations col
                WHERE [key].table_relation_id = col.table_relation_id
                ORDER BY ordinal_position -- tu chyba powinien być inny order?
                FOR XML path('')
                ) relation
            , description
        FROM cte_relations [key]
        )
    INSERT INTO dbo.erd_links (
        [module_id]
        , [relation_id]
        , [label_pos_x]
        , [label_pos_y]
        , [show_label]
        , [hidden]
        , [link_style]
        , [show_join_condition]
        )
    SELECT DISTINCT mods.dst_module_id
        , dst_relations.table_relation_id
        , src_links.[label_pos_x]
        , src_links.[label_pos_y]
        , src_links.[show_label]
        , src_links.[hidden]
        , src_links.[link_style]
        , src_links.[show_join_condition]
    FROM cte_keycols dst_relations
    INNER JOIN cte_keycols src_relations
        ON src_relations.relation = dst_relations.relation
    INNER JOIN dbo.erd_links src_links
        ON src_relations.table_relation_id = src_links.relation_id
    INNER JOIN @modules mods
        ON mods.src_module_id = src_links.module_id
    WHERE src_relations.pkdbid = @src_db_id
        AND src_relations.fkdbid = @src_db_id
        AND dst_relations.pkdbid = @dst_db_id
        AND dst_relations.fkdbid = @dst_db_id;
END;
GO

CREATE PROCEDURE dbo.copy_doc_by_id @src_db_id AS INT --source documentation id
    , @dst_db_id AS INT --destination documentation id
    , @create_user_obj AS BIT = 0 --remove from dest user objects not in source; add to dest user objects from source
AS
BEGIN
    --create user objects?
    IF @create_user_obj = 1
        EXECUTE copy_user_objects @src_db_id
            , @dst_db_id;

    --copy descriptions
    --modules
    UPDATE dst_modules
    SET [description] = src_modules.[description]
        , [description_search] = src_modules.[description_search]
    FROM [dbo].[modules] dst_modules
    INNER JOIN [dbo].[modules] src_modules
        ON src_modules.[title] = dst_modules.[title]
    WHERE src_modules.[database_id] = @src_db_id
        AND dst_modules.[database_id] = @dst_db_id;

    --tables
    UPDATE dst_tables
    SET [description] = src_tables.[description]
        , [description_search] = src_tables.[description_search]
        , [title] = src_tables.[title]
    FROM [dbo].[tables] dst_tables
    INNER JOIN [dbo].[tables] src_tables
        ON src_tables.[schema] = dst_tables.[schema]
            AND src_tables.[name] = dst_tables.[name]
            AND src_tables.[object_type] = dst_tables.[object_type]
    WHERE src_tables.[database_id] = @src_db_id
        AND dst_tables.[database_id] = @dst_db_id

    --table columns
    UPDATE dst_columns
    SET [description] = src_columns.[description]
        , [title] = src_columns.[title]
    FROM [dbo].[columns] dst_columns
    INNER JOIN [dbo].[tables] dst_tables
        ON dst_columns.[table_id] = dst_tables.[table_id]
    INNER JOIN [dbo].[tables] src_tables
        ON src_tables.[schema] = dst_tables.[schema]
            AND src_tables.[name] = dst_tables.[name]
            AND src_tables.[object_type] = dst_tables.[object_type]
    INNER JOIN [dbo].[columns] src_columns
        ON src_tables.[table_id] = src_columns.[table_id]
            AND src_columns.[name] = dst_columns.[name]
    WHERE src_tables.[database_id] = @src_db_id
        AND dst_tables.[database_id] = @dst_db_id;

    --table relations
    WITH cte_relations
    AS (
        SELECT relation_columns.table_relation_id
            , relation_columns.ordinal_position
            , relations.[description]
            , pk_tables.[database_id] pkdbid
            , fk_tables.[database_id] fkdbid
            , CONCAT (
                pk_tables.[object_type]
                , '.'
                , pk_tables.[schema]
                , '.'
                , pk_tables.[name]
                , '.'
                , pk_columns.[name]
                , '='
                , fk_tables.[object_type]
                , '.'
                , fk_tables.[schema]
                , '.'
                , fk_tables.[name]
                , '.'
                , fk_columns.[name]
                , ';'
                ) keycol
        FROM [dbo].tables_relations relations
        INNER JOIN [dbo].tables_relations_columns relation_columns
            ON relations.table_relation_id = relation_columns.table_relation_id
        INNER JOIN columns fk_columns
            ON relation_columns.column_fk_id = fk_columns.column_id
        INNER JOIN columns pk_columns
            ON relation_columns.column_pk_id = pk_columns.column_id
        INNER JOIN tables fk_tables
            ON fk_tables.table_id = relations.fk_table_id
        INNER JOIN tables pk_tables
            ON pk_tables.table_id = relations.pk_table_id
        )
        , cte_keycols
    AS (
        SELECT pkdbid
            , fkdbid
            , table_relation_id
            , (
                SELECT col.keycol AS [text()]
                FROM cte_relations col
                WHERE [key].table_relation_id = col.table_relation_id
                ORDER BY ordinal_position -- tu chyba powinien być inny order?
                FOR XML path('')
                ) relation
            , description
        FROM cte_relations [key]
        )
    UPDATE tables_relations
    SET description = src_relations.description
    FROM [dbo].tables_relations relations
    INNER JOIN cte_keycols dst_relations
        ON relations.table_relation_id = dst_relations.table_relation_id
    INNER JOIN cte_keycols src_relations
        ON src_relations.relation = dst_relations.relation
    WHERE src_relations.pkdbid = @src_db_id
        AND src_relations.fkdbid = @src_db_id
        AND dst_relations.pkdbid = @dst_db_id
        AND dst_relations.fkdbid = @dst_db_id;

    --unique constraints
    WITH cte_constraints
    AS (
        SELECT con_cols.unique_constraint_id
            , tabs.database_id AS [doc_id]
            , constraints.primary_key
            , constraints.[description]
            , tabs.[object_type]
            , tabs.database_id
            , tabs.[schema] AS tab_schema
            , tabs.[name] AS tab_name
            , cols.[name] AS [col_name]
        FROM [dbo].[unique_constraints] constraints
        INNER JOIN [dbo].[unique_constraints_columns] con_cols
            ON constraints.unique_constraint_id = con_cols.unique_constraint_id
        INNER JOIN [dbo].[columns] cols
            ON con_cols.column_id = cols.column_id
        INNER JOIN [dbo].[tables] tabs
            ON tabs.table_id = constraints.table_id
        WHERE constraints.[status] = 'A'
            AND con_cols.[status] = 'A'
            AND cols.[status] = 'A'
            AND tabs.[status] = 'A'
        )
        , cte_concols
    AS (
        SELECT unique_constraint_id
            , [doc_id]
            , tab_schema
            , tab_name
            , primary_key
            , [description]
            , object_type
            , database_id
            , stuff((
                    SELECT CONCAT (
                            ', '
                            , col.[col_name]
                            ) AS [text()]
                    FROM cte_constraints col
                    WHERE [key].unique_constraint_id = col.unique_constraint_id
                    ORDER BY col.[col_name]
                    FOR XML PATH('')
                    ), 1, 2, '') col
        FROM cte_constraints [key]
        GROUP BY unique_constraint_id
            , [doc_id]
            , tab_schema
            , tab_name
            , primary_key
            , [description]
            , object_type
            , database_id
        )
    UPDATE dst_uk
    SET [description] = src_concols.[description]
    FROM [dbo].[unique_constraints] dst_uk
    INNER JOIN cte_concols dst_concols
        ON dst_uk.unique_constraint_id = dst_concols.unique_constraint_id
    INNER JOIN cte_concols src_concols
        ON (
                src_concols.tab_schema = dst_concols.tab_schema
                AND src_concols.tab_name = dst_concols.tab_name
                AND src_concols.col = dst_concols.col
                AND src_concols.primary_key = dst_concols.primary_key
                AND src_concols.object_type = dst_concols.object_type
                )
    WHERE dst_concols.[database_id] = @dst_db_id
        AND src_concols.[database_id] = @src_db_id;

    --triggers
    UPDATE dst_triggers
    SET [description] = src_triggers.[description]
    FROM [dbo].[triggers] dst_triggers
    INNER JOIN [dbo].[triggers] src_triggers
        ON dst_triggers.[name] = src_triggers.[name]
    INNER JOIN [dbo].[tables] dst_tables
        ON dst_triggers.table_id = dst_tables.table_id
    INNER JOIN [dbo].[tables] src_tables
        ON src_triggers.table_id = src_tables.table_id
            AND src_tables.[schema] = dst_tables.[schema]
            AND src_tables.[name] = dst_tables.[name]
            AND src_tables.[object_type] = dst_tables.[object_type]
    WHERE dst_tables.[database_id] = @dst_db_id
        AND src_tables.[database_id] = @src_db_id;

    --procedures and functions
    UPDATE dst_procedures
    SET [description] = src_procedures.[description]
        , [description_search] = src_procedures.[description_search]
        , [title] = src_procedures.[title]
    FROM [dbo].[procedures] dst_procedures
    INNER JOIN [dbo].[procedures] src_procedures
        ON src_procedures.[schema] = dst_procedures.[schema]
            AND src_procedures.[name] = dst_procedures.[name]
            AND src_procedures.[object_type] = dst_procedures.[object_type]
    WHERE dst_procedures.[database_id] = @dst_db_id
        AND src_procedures.[database_id] = @src_db_id;

    --parameters
    UPDATE dst_parameters
    SET [description] = src_parameters.[description]
    FROM [dbo].[parameters] dst_parameters
    INNER JOIN [dbo].[parameters] src_parameters
        ON dst_parameters.[name] = src_parameters.[name]
            AND dst_parameters.[parameter_mode] = src_parameters.[parameter_mode]
    INNER JOIN [dbo].[procedures] dst_procedures
        ON dst_parameters.procedure_id = dst_procedures.procedure_id
    INNER JOIN [dbo].[procedures] src_procedures
        ON src_parameters.procedure_id = src_procedures.procedure_id
            AND src_procedures.[schema] = dst_procedures.[schema]
            AND src_procedures.[name] = dst_procedures.[name]
            AND src_procedures.[object_type] = dst_procedures.[object_type]
    WHERE dst_procedures.[database_id] = @dst_db_id
        AND src_procedures.[database_id] = @src_db_id;
END;
GO

CREATE PROCEDURE dbo.copy_doc @src_title AS NVARCHAR(250) -- source documentation name, should be unique
    , @dst_title AS NVARCHAR(250) -- destination documentation name, should be unique
    , @create_user_obj AS INT -- 0:do not touch user created objects; 1:remove user objects not in source and add user objects from source
AS
DECLARE @src_db_id INT
    , @dst_db_id INT

BEGIN
    SET @src_db_id = (
            SELECT TOP 1 [database_id]
            FROM [dbo].[databases]
            WHERE [title] = @src_title
            );
    SET @dst_db_id = (
            SELECT TOP 1 [database_id]
            FROM [dbo].[databases]
            WHERE [title] = @dst_title
            );

    IF @src_db_id IS NULL
        OR @dst_db_id IS NULL
        OR @src_db_id = @dst_db_id
        SELECT 'Operation failed. Couldn''t find either source or destination documentation.' AS Error;
    ELSE
        EXECUTE dbo.copy_doc_by_id @src_db_id
            , @dst_db_id
            , @create_user_obj;
END;
GO


