devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SqlServer通过存储过程同步数据库表结构_sqlserver利用存储过程实时同步表

作者：知新_RL | 2024-02-27 21:19:04

赞

踩

sqlserver利用存储过程实时同步表

公司以前同步数据库表结构，都是通过一个很大的create脚本去远程服务器执行(因为远程服务器不允许发布订阅)。但是这样执行的缺点有如下几点:

  1. 需要登录每一个服务器，过程比较麻烦。
  2. 无法保证导入所有表结构。
  3. 需要耗费大量人力物力，后期维护麻烦。
    以上所述，自己写了一个存储过程来完成远程数据库表结构的同步。流程如下
    sqlserver同步数据库存储过程流程图链接(百度脑图)
    ① 创建一张存放远程服务器的表，表结构创建语句如下，表名：OMS_Product_DB_Info:
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OMS_Product_DB_Info]') AND type IN ('U'))
    DROP TABLE [dbo].[OMS_Product_DB_Info]
GO

CREATE TABLE [dbo].[OMS_Product_DB_Info] (
  [ID] bigint  IDENTITY(1,1) NOT NULL,
  [CustomerName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [ServerName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [LoginUser] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [LoginPassword] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [OmsServerName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [OdsServerName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [OmsHistoryServerName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [OmsDwServerName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [ServerArea] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [Description] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [Synchronous] bit  NULL,
  [UpdateTime] nvarchar(30) COLLATE Chinese_PRC_CI_AS  NULL
)
GO
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

②向OMS_Product_DB_Info这个表中插入远程服务器的信息(例如，链接地址,端口号，用户名，密码等等信息)

INSERT INTO [dbo].[OMS_Product_DB_Info] ([CustomerName], [ServerName], [LoginUser], [LoginPassword], [OmsServerName], [OdsServerName], [OmsHistoryServerName], [OmsDwServerName], [ServerArea], [Description], [Synchronous], [UpdateTime]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
  • 1

③ 登录远程服务器，执行同步需要的脚本。本来整个过程是不需要登录远程服务器就能同步表结构。但是因为我们的脚本大于了sqlserver的一个字段存放的最大容量。所以暂时就这样操作，以后在优化，执行脚本见下:

--生成OMS_Sync_Table_Structure表
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OMS_Sync_Table_Structure]') AND type IN ('U'))
    DROP TABLE [dbo].[OMS_Sync_Table_Structure]
GO

CREATE TABLE [dbo].[OMS_Sync_Table_Structure] (
  [TableName] sysname  NOT NULL,
  [ColumnName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [colType] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [isCanNull] int  NULL,
  [columntype] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [ColLength] int  NULL,
  [Id] int  NOT NULL,
  [colorder] smallint  NULL
)
GO
--生成OMS_SYNC_TABLES表
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OMS_SYNC_TABLES]') AND type IN ('U'))
    DROP TABLE [dbo].[OMS_SYNC_TABLES]
GO
CREATE TABLE [dbo].[OMS_SYNC_TABLES](
    [TableName] [nvarchar](255) NULL,
    [CreateSql] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
--生成OMS_Table_Not_Exists表
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[OMS_Table_Not_Exists]') AND type IN ('U'))
    DROP TABLE [dbo].[OMS_Table_Not_Exists]
GO
CREATE TABLE [dbo].[OMS_Table_Not_Exists](
    [TableName] [nvarchar](255) NULL
) ON [PRIMARY]

GO
         --  执行过程
         -- 1. 创建表结构视图
    IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[V_View_OrderColumns]') AND type IN ('V'))
        DROP VIEW [dbo].[V_View_OrderColumns]
    GO

        Create View [dbo].[V_View_OrderColumns] as 
                        SELECT Obj.name AS TableName, col.name AS ColumnName, typ.name + CASE typ.name
        WHEN 'decimal' THEN '(' + CAST(col.xprec AS varchar) + ',' + CAST(col.xscale AS varchar) + ')'
        WHEN 'nvarchar' THEN '(' + CASE 
            WHEN col.length = '-1' THEN 'max'
            ELSE CAST(col.length / 2 AS varchar)
        END + ')'
        WHEN 'varchar' THEN '(' + CASE 
            WHEN col.length = '-1' THEN 'max'
            ELSE CAST(col.length AS varchar)
        END + ')'
        ELSE ''
    END AS colType, col.isnullable AS isCanNull
    , typ.name AS columntype
    , CASE typ.name
        WHEN 'nvarchar' THEN 
            CASE 
                WHEN col.length = '-1' THEN 2000
                ELSE col.length / 2
            END
        WHEN 'varchar' THEN 
            CASE 
                WHEN col.length = '-1' THEN 2000
                ELSE col.length
            END
    END AS ColLength, obj.Id, col.colorder
FROM sysobjects obj
    LEFT JOIN syscolumns col ON obj.id = col.id
    LEFT JOIN systypes typ ON col.xtype = typ.xtype
WHERE obj.xtype = 'U'
    AND typ.status = 0
        GO

    --创建字段索引视图
    IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[V_View_ObjectIndex]') AND type IN ('V'))
        DROP VIEW [dbo].[V_View_ObjectIndex]
    GO
        CREATE VIEW [dbo].[V_View_ObjectIndex]
    AS
    SELECT a.id AS 'ID', a.name AS 'tableName', b.name AS 'colName', d.name AS 'indexName'
    FROM sysobjects a, sys.syscolumns b, sys.index_columns c, sys.sysindexes d
    WHERE a.xtype = 'u'
        AND a.id = b.id
        AND a.id = c.object_id
        AND b.colid = c.column_id
        AND a.id = d.id
        AND c.index_id = d.indid
        GO


    IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[P_BU_Sync_OMS_Table_Structure]') AND type IN ('P'))
        DROP PROCEDURE [dbo].[P_BU_Sync_OMS_Table_Structure]
    GO
        --2.创建P_BU_Sync_OMS_Table_Structure存储过程
        -- 存储过程： 同步表结构
        CREATE Procedure [dbo].[P_BU_Sync_OMS_Table_Structure]
        (
            @TblName nVarchar(255) = 'SalesOrder'
        )
        As
        Begin
        Print '-------------------------------------------------------'
        Print '**=Start=**  Sync Table : ' + @TblName + ' Begin. ';

         Declare @AlterSql nVarchar(4000),@ToTblName nVarchar(200),
         @ToTblDataType nvarchar(20),@ToTblCharLength nvarchar(20),
         @FrTblDataType nvarchar(20),@FrTblCharLength nvarchar(20),
         @ColType nvarchar(20),@ColumnName nvarchar(20)

         -- 已有字段结构调整, 新增字段, 删除字段
         -- 遍历更新有变更的字段结构
         Declare AlterCol Cursor For
            Select Frtbl.ColumnName,Frtbl.colType,Frtbl.columntype,Frtbl.ColLength,ToTbl.columntype,ToTbl.ColLength,
                Case When FrTbl.ColumnName is not null And ToTbl.ColumnName is not null Then 'Alter Table DBO.' + FrTbl.TableName + ' Alter Column ' + FrTbl.ColumnName + ' ' + FrTbl.colType + ';'
                When FrTbl.ColumnName is not null Then 'Alter Table DBO.' + FrTbl.TableName + ' Add ' + FrTbl.ColumnName + ' ' + FrTbl.colType + ';'
                When ToTbl.ColumnName is not null Then 'Alter Table DBO.' + FrTbl.TableName +';' End As AlterSql
          From OMS_Sync_Table_Structure FrTbl(nolock)
          Left Join V_View_OrderColumns ToTbl(nolock) on FrTbl.TableName = ToTbl.TableName And FrTbl.ColumnName = ToTbl.ColumnName
          Where FrTbl.TableName = @TblName
          And isnull(FrTbl.colType, '') <> isnull(ToTbl.colType, '')
          Order By FrTbl.ColOrder

         Open AlterCol
         Fetch next From AlterCol Into @ColumnName,@ColType,@FrTblDataType,@FrTblCharLength,@ToTblDataType,@ToTblCharLength,@AlterSql;
         While @@FETCH_STATUS = 0
            Begin
                --新增
                IF(CHARINDEX('alter',substring(@AlterSql,6,len(@AlterSql))) = 0)
                    BEGIN
                        Print @AlterSql;
                        Exec SP_EXECUTESQL @AlterSql;
                    END
                ELSE
                    BEGIN
                    IF EXISTS (SELECT * FROM V_View_ObjectIndex WHERE tableName = @TblName AND colName = @ColumnName)
                    BEGIN
                        --如果数据类型相同并且修改长度大于现在的长度(修改长度)
                        IF (@ToTblDataType = @FrTblDataType AND CONVERT(int,@FrTblCharLength)>= CONVERT(int,@ToTblCharLength))
                            BEGIN
                                Print @AlterSql;
                                Exec SP_EXECUTESQL @AlterSql;
                            END
                        END
                    ELSE
                        Begin
                            IF (@ToTblDataType = @FrTblDataType AND CONVERT(int,@FrTblCharLength)>= CONVERT(int,@ToTblCharLength))
                                BEGIN
                                Print @AlterSql;
                                Exec SP_EXECUTESQL @AlterSql;
                                END;
                        End;  
                    END;

            Fetch next From AlterCol Into @ColumnName,@ColType,@FrTblDataType,@FrTblCharLength,@ToTblDataType,@ToTblCharLength,@AlterSql;
            End;
         Close AlterCol;
         Deallocate AlterCol;
         Print '**=End=**  Sync Table : ' + @TblName + ' Completed. ';
         Print '-----------===========================---------------';
        End;
        GO
        IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[P_BU_Sync_OMS_Structure]') AND type IN ('P'))
            DROP PROCEDURE [dbo].[P_BU_Sync_OMS_Structure]
            GO
        --3.创建P_BU_Sync_OMS_Structure存储过程
        --执行存储过程 [dbo].[P_BU_Sync_OMS_Structure] 同步 表: OMS_Sync_Table_Structure 中所有表的表结构． 
        -- Proc1:
        CREATE Procedure [dbo].[P_BU_Sync_OMS_Structure] 
        As
        Begin   

         Declare @AlterSql nVarchar(4000), @TblName nVarchar(200),@SQL nVarchar(max)

         Truncate Table OMS_Table_Not_Exists;

         -- 已有字段结构调整, 新增字段, 删除字段
         -- 遍历更新有变更的字段结构
         Declare TabCur Cursor For
        Select Distinct TableName
        From OMS_Sync_Table_Structure
        Order By TableName
         Open TabCur
         Fetch next From TabCur Into @TblName;
         While @@FETCH_STATUS = 0
         Begin
          -- 更新字段结构
          Print @AlterSql;

          If Not Exists(Select 1 From Sys.sysobjects Where xtype = 'U' And name = @TblName)
          --Create New Table
            Begin
                   Select Top 1 @SQL = CreateSql
                    From OMS_SYNC_TABLES WHERE TableName = @TblName
                Print 'Insert Into CreateTabelSql :'+@SQL;
                Exec SP_EXECUTESQL @SQL;
            End

          If Exists (Select 1 From Sys.sysobjects Where xtype = 'U' And name = @TblName)
            Begin 
                Exec [dbo].[P_BU_Sync_OMS_Table_Structure] @TblName;
            End 
          Fetch next From TabCur Into @TblName;
         End;

         Close TabCur;
         Deallocate TabCur;
        End; 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211

④调用存储过程，[dbo].[P_BU_Sync_OMS_Structure]。P_BU_Sync_OMS_Structure代码如下

CREATE Procedure [dbo].[P_BU_Sync_OMS_Structure] 
        As
        Begin   

         Declare @AlterSql nVarchar(4000), @TblName nVarchar(200),@SQL nVarchar(max)

         Truncate Table OMS_Table_Not_Exists;

         -- 已有字段结构调整, 新增字段, 删除字段
         -- 遍历更新有变更的字段结构
         Declare TabCur Cursor For
        Select Distinct TableName
        From OMS_Sync_Table_Structure
        Order By TableName
         Open TabCur
         Fetch next From TabCur Into @TblName;
         While @@FETCH_STATUS = 0
         Begin
          -- 更新字段结构
          Print @AlterSql;

          If Not Exists(Select 1 From Sys.sysobjects Where xtype = 'U' And name = @TblName)
          --Create New Table
            Begin
                   Select Top 1 @SQL = CreateSql
                    From OMS_SYNC_TABLES WHERE TableName = @TblName
                Print 'Insert Into CreateTabelSql :'+@SQL;
                Exec SP_EXECUTESQL @SQL;
            End

          If Exists (Select 1 From Sys.sysobjects Where xtype = 'U' And name = @TblName)
            Begin 
                Exec [dbo].[P_BU_Sync_OMS_Table_Structure] @TblName;
            End 
          Fetch next From TabCur Into @TblName;
         End;

         Close TabCur;
         Deallocate TabCur;
        End;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

注意事项:要访问远程服务器需要配置远程服务器的安全组规则，不然可能导致无法访问。这个存储过程主要是针对公司里面的数据库同步，可能不能满足其他公司的需求。希望用的人只是借鉴我的想法和提出修改意见。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/155971
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号