使用SQL-DMO实现定制SQL Scripts

来源：互联网发布：媒体关注度知乎编辑：程序博客网时间：2024/05/05 12:06

      我是个负责数据库应用的开发人员，昨夜和旧同事电话聊天中谈到了一个
大家共同的苦恼：因为几乎每天都要将自己的数据库项目生成 SQL Scripts
以供备份存档或发给客户更新，所以生成 SQL Scripts就成了每天的必修课。
而 SQLSERVER的企业管理器又无法支持将生成 SQL Scripts的过程保存为一个设置
文件，每次都必须做许多相同的工作，
如：选择对象呀、设置格式呀、表脚本选项、文件选项。。老是重复这样的操作
实在太麻烦了！电话闲聊完后就想：能不能写个小工具，在可以提供 SQLSERVER
企业管理器所支持所有功能外，再增加个支持将各个选项的设置保存为
配置文件（如：ini或XM格式）的功能呢？这样我以后只要选取不同的配置文件就可以
直接生成出自己需要的Scripts格式，那样多好呀。。。
      但如何实现Scripts的生成呢？就考虑使用下以前很少用的 SQL-DMO吧！
心动不如行动，虽然夜已深，但有新鲜的体验总让我精神抖擞。。。呵呵，好了
不废话了。。就先简单地谈谈如何使用 SQL-DMO来实现自己的需求吧~~~~
      (代码示例使用Pascal语言表述)

      1. 枚举局域网内所有的 SQLSERVER实例
      函数：
           function GetAllServerInstances :TStringList;
           var
             oApplication ,
             oSeverNameList : Variant ;
             i : integer ;
             sNameList : TStringList ;
           begin
             sNameList := TStringList.Create ;
             oApplication := CreateOLEObject ('SQL DMO.Application') ;
             oSeverNameList := oApplication.ListAvailable SQLServers ;
             for i:= 1 to oSeverNameList.Count do
               sNameList.Append (oSeverNameList.Item(i)) ;
             Result := sNameList ;
             oApplication := NULL;
             oSeverNameList := NULL;
           end;

      2.登陆到指定的 SQLSERVER实例
      函数：
           function LoginServerInstances (sServer,sUser,sPassword :string):Variant;
           var
             o SQLServer : Variant ;
           begin
             o SQLServer := CreateOLEObject ('SQL DMO.SQLServer');
             o SQLServer.LoginTimeout := 30;
             o SQLServer.LoginSecure := False;
             o SQLServer.AutoReconnect := True;
             o SQLServer.Connect(sServer,sUser,sPassword);
             Result := o SQLServer;
             o SQLServer := NULL ;
           end;

以上两个函数可以帮助我们完成取得局域网内所有的 SQLSERVER实例并且
登陆上一个指定的实例。接下来就看看如何取得指定数据库的表名称和存储
过程名称：

       3.枚举指定 SQLserver实例所有database的名称
       函数：
           function GetAllDatabaseNameList (o SQLServer : Variant ):TStringList;
           var
             sNameList : TStringList ;
             i : integer;
           begin
             sNameList := TStringList.Create ;
             for i:= 1 to o SQLServer.Databases.Count do
               sNameList.Append (o SQLServer.Databases.Item(i).Name) ;
             Result := sNameList ;
           end;

       4.枚举指定数据库所有Table的名称
       函数:
            function GetAllTableNameList (o SQLServer : Variant ; sDataBaseName : string ):TStringList;
            var
              oDatabase : Variant ;
              sNameList : TStringList ;
              i : integer;
            begin
              sNameList := TStringList.Create ;
              oDatabase := CreateOLEObject ('SQL DMO.Database');
              oDatabase := o SQLServer.Databases.Item(sDataBaseName);
              for i:= 1 to oDatabase.Tables.Count do
                sNameList.Append (oDatabase.Tables.Item(i).Name) ;
              Result := sNameList ;
              oDatabase := Null ;
            end;

       5.枚举指定数据库所有StoreProcedure的名称
       函数:
            function GetAllStoreProcedureNameList (o SQLServer : Variant ; sDataBaseName : string ):TStringList;
            var
              oDatabase : Variant ;
              sNameList : TStringList ;
              i : integer;
            begin
              sNameList := TStringList.Create ;
              oDatabase := CreateOLEObject ('SQL DMO.Database');
              oDatabase := o SQLServer.Databases.Item(sDataBaseName);
              for i:= 1 to oDatabase.StoredProcedures.Count do
                sNameList.Append (oDatabase.StoredProcedures.Item(i).Name) ;
              Result := sNameList ;
              oDatabase := Null ;
            end;

      通过以上3个函数就可以把生成Scripts前的准备工作做好了，然后就可以开始
着重了解如何生成Scripts了。Scripts也同样是 SQL-DMO某些对象的Method。这些
对象如：Database，View ，StoredProcedure ，Trigger ，Check 等等。
Scripts Method 的Syntax是：
    object.Script( [ ScriptType ] [, ScriptFilePath ] [, Script2Type ] ) as String
ScriptType (integer type) 参数是来设定产生什么内容的Scripts ，如对其赋值 SQL DMOScript_Default，
这样就产生的是该对象普通的脚本，如StoredProcedure object，那肯定就是
其create StoredProcedure 的内容咯。如果赋值 SQL DMOScript_Drops的话，
那就产生的是drop object的内容。关于ScriptType，Script2Type (integer type)
这个参数的详细使用可以参阅 : C:Program FilesMicrosoft SQL Server80ToolsBooks SQL DMO.chm
的帮助文件。Script方法的返回值是文本类型，不管你填不填ScriptFilePath参数，
你都能得到该文本，也就是Script 的内容。

      而Table ，UserDefinedDatatype 这两个对象生成Scripts的Method比较特殊，
它们调用的是Generate SQL Method，其Syntax是：
    object.Generate SQL( Database ) as String ，比较简单没有过多的参数，返回值
也是Script 的内容。

      建议：把ScriptType，Script2Type两个参数可以赋予的值做成常量，这样在程序
中调用更直观些。如：SQL DMOScript_DatabasePermissions = 32;
SQL DMOScript_Default = 4; ....

根据上面的说明可以建立这个函数（在申明此函数时填加overload表示同名不同参函数的重载）：
    file://6.生成指定Objects的 SQL Scripts
    file://(o SQLObjcet : Database，View ，StoredProcedure ，Trigger ，Check)
    // sScriptFilePath defalut is ''
    function GetThisObjectScripts (o SQLObjcet : Variant ;sScriptFilePath : string ;
                                   iScriptType,iScript2Type : integer) : string ;
    begin
      Result := o SQLObjcet.Script(iScriptType,sScriptFilePath,iScript2Type) ;
    end;
    file://7.生成指定Objects的 SQL Scripts
      // overload Method
      file://(o SQLObjcet : Table ，UserDefinedDatatype)
    function TForm1.GetThisObjectScripts (oDatabase ,o SQLObjcet : Variant ) : string ;
    begin
      Result := o SQLObjcet.Generate SQL(oDatabase) ;
    end;

调用的时候可以根据界面选择生成什么样格式的对象就传入相应的参数
写个简单的例子，取出master的存储过程MS_SQLctrs_users的内容：
     procedure TForm1.Button4Click(Sender: TObject);
     var
       o SQLServer,
       oDatabase,
       oStoredProcedure : Variant ;
       ReturnStr : string;
     begin
       oDatabase := CreateOLEObject ('SQL DMO.Database');
       o SQLServer := LoginServerInstances('DAVID','sa','');
       oDatabase := o SQLServer.Databases.Item('master');
       oStoredProcedure := o SQLServer.Databases.Item('master').StoredProcedures.Item('MS_SQLctrs_users');
       file://生成create procedure的Scripts，将第一个4换成1就成了drop procedure的 Scripts了
       ReturnStr := GetThisObjectScripts(oStoredProcedure,'',4,4);
       ShowMessage(ReturnStr);
     end;

如果多个对象都需要导出 Scripts 话，可以用个string 变量保存被循环调用的
GetThisObjectScripts过程，就可以实现导出多个对象的脚本了！

关于保存和取出配置生成Scripts的过程，我想不用详细描述了吧？
只需把设置好的控件状态保存到配置文件中，并且可以调用配置文件
反向对控件状态来设置，这样就简单的实现了自定义的过程呀。
呵呵，到这里就进入尾声了，通过组合上面这些函数，
并且设置合理的界面调用，这程序应该很容易完成吧~~~~
既然这样，那让我们为提供自己工作效率而建立更多小工具呀！

更多详情，请参阅 :
C:Program FilesMicrosoft SQL Server80ToolsBooks SQL DMO.chm