SqlServer通過存儲過程同步資料庫表結構

公司以前同步資料庫表結構，都是通過一個很大的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(,) NOT NULL,
  [CustomerName] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [ServerName] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [LoginUser] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [LoginPassword] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [OmsServerName] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [OdsServerName] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [OmsHistoryServerName] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [OmsDwServerName] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [ServerArea] varchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [Description] varchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [Synchronous] bit  NULL,
  [UpdateTime] nvarchar() 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() COLLATE Chinese_PRC_CI_AS  NULL,
  [colType] nvarchar() COLLATE Chinese_PRC_CI_AS  NULL,
  [isCanNull] int  NULL,
  [columntype] nvarchar() 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]() 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]() NULL
) ON [PRIMARY]

GO
         --  執行過程
         --  建立表結構視圖
    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 /  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 
                ELSE col.length / 
            END
        WHEN 'varchar' THEN 
            CASE 
                WHEN col.length = '-1' THEN 
                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 = 
        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
        --建立P_BU_Sync_OMS_Table_Structure存儲過程
        -- 存儲過程： 同步表結構
        CREATE Procedure [dbo].[P_BU_Sync_OMS_Table_Structure]
        (
            @TblName nVarchar() = '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,,len(@AlterSql))) = )
                    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 :'[email protected];
                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(), @TblName nVarchar(),@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  From Sys.sysobjects Where xtype = 'U' And name = @TblName)
          --Create New Table
            Begin
                   Select Top  @SQL = CreateSql
                    From OMS_SYNC_TABLES WHERE TableName = @TblName
                Print 'Insert Into CreateTabelSql :'[email protected]SQL;
                Exec SP_EXECUTESQL @SQL;
            End

          If Exists (Select  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;

注意事項:要通路遠端伺服器需要配置遠端伺服器的安全組規則，不然可能導緻無法通路。這個存儲過程主要是針對公司裡面的資料庫同步，可能不能滿足其他公司的需求。希望用的人隻是借鑒我的想法和提出修改意見。

SqlServer通過存儲過程同步資料庫表結構

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述