SQL应用之跨数据库服务器数据库结构同步实现（MSSQL）

一、前言

什么是“跨数据库服务器数据库结构同步”？

跨数据库服务器数据库结构同步，这里包含以下几个方面的问题

• 跨数据库服务器：两个数据库分别位于不同的数据库服务器
• 数据库结构：由数据库对象（表、视图、存储过程、函数等）组成的数据库结构
• 同步：要求将一个数据库结构的修改更新到另外一个数据库中，这里的修改可以是数据库对象的创建、删除、数据类型的修改等等

“跨数据库服务器的数据库结构同步”到底有什么用呢？
在发布系统时，经常要将开发库的数据库结构（表结构、视图、存储过程等）同步到生产库中。尽管也有相应的文档和规范，要求数据库修改必须要有记录，但是根据实践发现，总会有一些”漏网之鱼”。这样就急需一个自动发现数据库结构修改的工具——数据库结构差异发现。

开发环境与生产环境是分离开的，因此还要处理数据库异地的问题。异地数据库结构的同步，还会涉及执行权限的问题。

二、远程数据库结构同步实现方案

这个实现是本人几年前所作，其设计与实现思路请详见下文中的备注。备注中记录了实现过程中遇到的一些问题。

使用说明：
- @sserver：表示远程服务器所在IP，如两数据库都在本地，置空即可
- @sdbname：表示源数据库名称
- @tserver：表示目标数据库所在服务器的IP
- @tdbname：表示目标数据库名称

注意：如涉及远程服务器，请先创建“链接服务器”，详情请查看 SQL总结之跨数据库服务器之间的数据访问

警告：本实现方案只经过了本人简单测试，分享出来仅供学习探讨。在生产环境中请谨慎使用！

-- ==========================================================
-- /******************* 数据库结构同步 *******************/
-- 1、同步的范围：视图、存储过程、函数、触发器，也包括数据库表结构
-- 2、思路：
--  (1)计算出视图、存储过程、函数、触发器、表（索引、主键、外键、默认值约束、唯一约束、Check约束）等对象的依赖关系
--  (2)获取脚本对象(视图、存储过程、函数、触发器)的创建脚本、表内部对象(索引、主键、外键、默认值约束、唯一约束、Check约束)创建所必须的相关参数
--  (3)表字段等元数据信息
--  (4)比较对象，需要执行的操作(add、modify)
--  (5)对于modify的（视图、存储过程、函数、触发器）对象，更新前将脚本备份到txt中，然后再执行更新脚本操作
--  (6)对于add操作的字段，如果所属表不存在，不作操作
--  (7)更新失败后，可以通过txt恢复结构
--  (8)表与字段的备注信息也要更新
--  (9)预防措施：<1>数据库备份；<2>将需要更新的对象脚本保存到txt；<3>错误信息日志；
-- 3、注意
--  (1)视图、存储过程、函数、触发器修改名称时，请即时右键修改脚本重新执行一次（对象修改名称不触发创建脚本中对象名称的修改）
--  (2)默认schema_name为dbo，不支持自定义的其它schema_name
--  (3)不支持自定义数据类型type的更新
--  (4)暂不支持Synonyms的更新（目前用不着）
-- /*********************************************/
-- /************ 将数据库清空重置 *****************/
-- 1、功能范围：(1)删除数据   (2)将表的自增ID重置到0
-- 2、问题：数据库表相互依赖，不易清空数据
-- 3、预防措施：清空数据前数据库备份
/************************************************/
-- ==========================================================
SET NOCOUNT ON;         -- 不显示影响行数
SET ANSI_WARNINGS OFF;  -- 不显示警告信息（聚合函数在遇到字段NULL值情况会出现警告）

declare @sserver varchar(50) /*= '[172.16.8.95]'*/,@sdbname varchar(50) = 'HRDB',
    @tserver varchar(50),@tdbname varchar(50) = 'HRDBDev',
    @scollation varchar(50) = cast((select SERVERPROPERTY(N'collation')) as varchar(50)),@tcollation varchar(50)
/*
 1、处理字符串：
    (1)动态SQL不支持点位符，输入变量作用有限，不可作表名用;
    (2)Replace字符串函数可以处理超长字符：注意，输入类型与返回类型保持一致，返回类型超出输入类型的最大长度时将被截取，因此最好输入类型设置max
    (3)print字符数量有限:varchar(max) 和 nvarchar(max) 数据类型均被截断为不大于 varchar(8000) 和 nvarchar(4000) 的数据类型
    set @execsql = REPLACE(@sql,'#tb','#t')
    set @execsql = REPLACE(@execsql,'[@dbname]',@source)
 2、用openquery行集函数取代动态服务器链接的访问方式（4秒--47秒)
    (1)动态链接访问方式(select * from [172.16.8.95].dbname.dbo.table)不要对远程对象使用元数据函数(object_name)，必须通过远程对象获取相应信息
    (2)动态服务器链接耗时47秒,而openquery用时4秒,可能的原因是动态服务器链接是将每个对象数据集从远程取出然后在本地处理，而不是在远程将数据集处
       理好后返回最终数据集
    (3)openquery行集函数中,第二个参数是查询sql,只能用常量且字符不超8000,需要使用动态SQL将变量变成常量再执行
*/
declare @sql varchar(max),@colsql varchar(max),@execsql nvarchar(max),@execDepSql nvarchar(max)
set @sql = 'select t.type,t.object_id,t.name,t.parent_object_id,t2.name as pname,
    case when t.type in(''P'',''V'',''TR'',''FN'',''TF'') then m.definition
        when t.type in(''PK'',''UQ'') then (case when t.type in(''PK'') then ''PRIMARY KEY'' else ''UNIQUE'' end) + '' ('' +
            STUFF((select '','' + col.name from [@dbname].sys.index_columns indcol
                        left join [@dbname].sys.columns col on col.object_id = t.parent_object_id and col.column_id = indcol.column_id
                    where t.type in(''PK'',''UQ'') and indcol.object_id = ind.object_id and indcol.index_id = ind.index_id 
                    for xml path('''')),1,1,'''') + '')''
        when t.type in(''F'') then (''FOREIGN KEY ('' +
            STUFF((select '','' + col.name from [@dbname].sys.foreign_key_columns kcol
                        left join [@dbname].sys.columns col on col.object_id = kcol.parent_object_id and col.column_id = kcol.parent_column_id
                    where t.type in(''F'') and kcol.constraint_object_id = k.object_id
                    for xml path('''')),1,1,'''') + '') REFERENCES '' 
            + t2.name + '' (''
            + STUFF((select '','' + rcol.name from [@dbname].sys.foreign_key_columns kcol
                        left join [@dbname].sys.columns rcol on rcol.object_id = kcol.referenced_object_id and rcol.column_id = kcol.referenced_column_id
                        where t.type in(''F'') and kcol.constraint_object_id = k.object_id
                    for xml path('''')),1,1,'''') + '') ''
            + (case when k.delete_referential_action = 0 then '''' else '' ON DELETE '' + k.delete_referential_action_desc collate Chinese_PRC_CI_AS end)
            + (case when k.update_referential_action = 0 then '''' else '' ON UPDATE '' + k.update_referential_action_desc collate Chinese_PRC_CI_AS end)
            + (case when k.is_not_for_replication = 1 then '' NOT FOR REPLICATION'' else '''' end))
        when t.type in(''D'') then ''DEFAULT '' + dcons.definition + '' FOR '' + dcons.colname
        when t.type in(''C'') then ''CHECK '' + (case when ckcons.is_not_for_replication = 1 then '' NOT FOR REPLICATION'' else '''' end)
             + ckcons.definition
        when t.type in(''U'') then ''CREATE TABLE '' + QUOTENAME(t.name) + '' ('' +
            REPLACE(STUFF((select '','' + CHAR(13) + CHAR(10) + CHAR(9) + col.name + '' '' +
                (case when tp.name in (''decimal'',''numeric'') then tp.name + ''('' + cast(col.precision as varchar) + '','' + cast(col.scale as varchar) + '')''
                    when tp.name in (''float'') and col.precision <> tp.precision then tp.name + ''('' + cast(col.precision as varchar) + '')''
                    when tp.name in (''binary'',''char'') and col.max_length <> 1 then tp.name + ''('' + cast(col.max_length as varchar) + '')''
                    when tp.name in (''varbinary'',''varchar'') and col.max_length <> 1 then tp.name + ''('' + (case when col.max_length = -1 then ''max'' else cast(col.max_length as varchar) end) + '')''
                    when tp.name in (''nchar'',''nvarchar'') and col.max_length <> 1 then tp.name + ''('' + (case when col.max_length = -1 then ''max'' else cast(col.max_length/2 as varchar) end) + '')''
                    else tp.name end) + 
                isnull('' '' +col.collation_name,'''') + 
                (case when col.is_nullable = 1 then '''' else '' NOT NULL'' end) +
                (case when col.is_identity = 1 then '' IDENTITY'' + (case when seed_value = 1 and increment_value = 1 then '''' else '' '' + cast(seed_value as varchar) + '','' + cast(increment_value as varchar) + '')'' end)  else '''' end) +
                (case when col.default_object_id <> 0 then '' DEFAULT '' + df.definition else '''' end) +
                (case when col.is_sparse = 1 then '' SPARSE'' else '''' end +
                (case when col.is_column_set = 1 then '' XML COLUMN_SET FOR ALL_SPARSE_COLUMNS'' else '''' end) +
                (case when col.is_computed = 1 then '' AS '' + cmp.definition else '''' end))
            from [@dbname].sys.columns col
                left join [@dbname].sys.types tp on tp.user_type_id = col.user_type_id
                left join [@dbname].sys.identity_columns iden on iden.object_id = col.object_id and iden.column_id = col.column_id
                left join [@dbname].sys.computed_columns cmp on cmp.object_id = col.object_id and cmp.column_id = col.column_id
                left join [@dbname].sys.default_constraints df on df.object_id = col.default_object_id
            where col.object_id = t.object_id for xml path('''')),1,1,''''),''&#x0D;'','''') + 
            ISNULL(REPLACE((select '','' + CHAR(13) + CHAR(10) + CHAR(9) + ''CONSTRAINT '' + ind.name + '' '' +
                        (case when o.type in(''PK'') then ''PRIMARY KEY'' else ''UNIQUE'' end) + '' ('' +
                        STUFF((select '','' + col.name from [@dbname].sys.index_columns indcol
                                    left join [@dbname].sys.columns col on col.object_id = ind.object_id and col.column_id = indcol.column_id
                                where indcol.object_id = ind.object_id and indcol.index_id = ind.index_id 
                                for xml path('''')),1,1,'''') + '')''
                from [@dbname].sys.indexes ind
                left join [@dbname].sys.objects o on o.name = ind.name
                where o.type in(''PK'',''UQ'') and ind.object_id = t.object_id for xml path('''')),''&#x0D;'',''''),'''') +
            ISNULL(REPLACE((select '','' + CHAR(13) + CHAR(10) + CHAR(9) + ''CONSTRAINT '' + k.name + '' FOREIGN KEY ('' +
                        STUFF((select '','' + col.name from [@dbname].sys.foreign_key_columns kcol
                                    left join [@dbname].sys.columns col on col.object_id = kcol.parent_object_id and col.column_id = kcol.parent_column_id
                                where t.type in(''F'') and kcol.constraint_object_id = k.object_id
                                for xml path('''')),1,1,'''') + '') REFERENCES '' 
                        + t.name + '' (''
                        + STUFF((select '','' + rcol.name from [@dbname].sys.foreign_key_columns kcol
                                    left join [@dbname].sys.columns rcol on rcol.object_id = kcol.referenced_object_id and rcol.column_id = kcol.referenced_column_id
                                    where t.type in(''