SqlServer通过存储过程同步数据库表结构

最新推荐文章于 2024-07-03 14:27:39 发布

poc999

最新推荐文章于 2024-07-03 14:27:39 发布

阅读量4.2k

点赞数 2

分类专栏：数据库文章标签： sqlserver 数据库同步存储过程

本文链接：https://blog.csdn.net/weixin_38179324/article/details/81486500

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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

需要登录每一个服务器，过程比较麻烦。
无法保证导入所有表结构。
需要耗费大量人力物力，后期维护麻烦。
以上所述，自己写了一个存储过程来完成远程数据库表结构的同步。流程如下
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

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

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

③ 登录远程服务器，执行同步需要的脚本。本来整个过程是不需要登录远程服务器就能同步表结构。但是因为我们的脚本大于了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;

④调用存储过程，[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;

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

poc999

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
SqlServer通过存储过程同步数据库表结构

公司以前同步数据库表结构，都是通过一个很大的create脚本去远程服务器执行(因为远程服务器不允许发布订阅)。但是这样执行的缺点有如下几点:需要登录每一个服务器，过程比较麻烦。无法保证导入所有表结构。需要耗费大量人力物力，后期维护麻烦。以上所述，自己写了一个存储过程来完成远程数据库表结构的同步。流程如下 sqlserver同步数据库存储过程流程图链接(百度脑图) ① 创建一张存放...
复制链接

扫一扫

专栏目录