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