数据库批量更新维护

情况：多台数据库服务器，每台服务器多个数据库，数据库结构一致（提供给不同客户使用）

一、手工操作

1、数据库少

下拉列表选择不同数据库，执行更新脚本sql.数据库少，操作感觉不到麻烦。

2、数据库多

下拉列表选择不同数据库，操作麻烦，容易遗漏更新数据库。

SQL Server Management Studio这个下拉列表，还不支持拉大缩小，数据库多了去选择不同数据库相当够呛。

二、半自动

思路：先更新一个数据库（NEWNEW）,比如更新了存储过程AddSaleOrder和DeleteSaleOrder, 和执行一段sql(DELETE FROM dbo.SystemConfig WHERE ConfigName='A')，然后通过sql参照这个数据库更新同台服务器的其他数据库。

USE [NEWNEW]
DECLARE @name SYSNAME
DECLARE userDB CURSOR FOR select name from master.dbo.sysdatabases WHERE SID <> 0x01
OPEN userDB
FETCH NEXT FROM userDB INTO @name
WHILE (@@FETCH_STATUS = 0)
   BEGIN
       EXEC('use [' + @name + '] IF (EXISTS(SELECT 1 FROM dbo.sysobjects o WHERE o.NAME = ''Customer'' AND o.xtype = ''U'')) BEGIN PRINT ''' + @name + ''' END')
       FETCH NEXT FROM userDB INTO @name
   END
CLOSE userDB
DEALLOCATE userDB

假如输出：

DB1

DB2

DB3

获取一台服务器上的所有自定义数据库，select name from master.dbo.sysdatabases WHERE SID <> 0x01
通过游标再次再次过滤需要的自定义数据库（一个特殊的对象，比如：Customer表）

生成批量更新数据库的脚本:

USE [NEWNEW]
DECLARE @objectID INT
DECLARE @objectName SYSNAME
DECLARE @ObjectType CHAR(2)
DECLARE @text VARCHAR(MAX)
DECLARE @dbName SYSNAME
DECLARE getName CURSOR SCROLL FOR select name from master.dbo.sysdatabases WHERE SID <> 0x01 AND NAME IN ('DB1' ,'DB2' ,'DB3')
OPEN getName
DECLARE getObj CURSOR FOR SELECT o.id, o.[name], o.xtype FROM dbo.sysobjects o WHERE o.NAME IN ('AddSaleOrder' ,'DeleteSaleOrder') AND o.xtype IN ('P', 'FN', 'V')
OPEN getObj
FETCH NEXT FROM  getObj INTO @objectID, @objectName, @ObjectType
WHILE (@@FETCH_STATUS = 0)
     BEGIN
          SET @text = ''
          SELECT @text = @text + s.[text] FROM dbo.syscomments s WHERE s.id = @objectID
          FETCH FIRST FROM getName INTO @dbName
          WHILE (@@FETCH_STATUS = 0)
               BEGIN
                   IF (@dbName = 'DB1')
                       USE [DB1]
                   ELSE IF (@dbName = 'DB2')
                       USE [DB2]
                   ELSE IF (@dbName = 'DB3')
                       USE [DB3]
                   IF (EXISTS(SELECT 1 FROM dbo.sysobjects o WHERE o.NAME = @objectName AND o.xtype = @ObjectType))
                       BEGIN
                         IF (@ObjectType = 'P')
                            EXEC ('DROP PROCEDURE dbo.' + @objectName)
                         ELSE IF(@ObjectType = 'V')
                            EXEC ('DROP VIEW dbo.' + @objectName)
                         ELSE IF(@ObjectType = 'FN')
                            EXEC ('DROP FUNCTION dbo.' + @objectName)
                       END
                   EXEC (@text)
                   EXEC ('DELETE FROM dbo.SystemConfig WHERE ConfigName=''A''')
                   USE [NEWNEW]
                   FETCH NEXT FROM getName INTO @dbName
               END
          FETCH NEXT FROM getObj INTO @objectID, @objectName, @ObjectType
     END
CLOSE getObj
DEALLOCATE getObj
CLOSE getName
DEALLOCATE getName

dbo.syscomments存储的存储过程脚本，如果脚本字符超过4000字符，会多行存储。@text = @text + s.[text]累加得到脚本，没个对象更新时SET @text = ''，清除

辅助winform:

源代码下载：

https://files.cnblogs.com/yinyunpan/WangshijieTool.rar

3、自动

向大家请教了。。。