如何脚本化 SQL Server 对象

ozkary

4.76/5 (18投票s)

2006年11月1日

CPOL

4分钟阅读

150706

1023

一个 VBScript 脚本，允许将所有 SQL Server 对象脚本化到每个独立的文件中

下载源代码 - 2.02 KB

引言

我经常需要将 SQL Server 数据库中的对象脚本化到文本文件中。我喜欢为每个对象创建一个脚本，并使用不同的文件扩展名来标识对象类型。这有很多种方法可以实现。可以通过 Enterprise Manager 连接到 SQL Server 并脚本化对象。也可以使用 Visual Studio 创建一个数据库项目，这也能让你脚本化对象。然而，如果你想自动化这个过程，这些方法就无效了。应该考虑一个持续集成流程，它能让你拥有一个干净的数据库构建过程，并包含最新的更改。另一个好处是能够将这些脚本文件维护在源代码控制仓库中。

目的/范围

本文的主要目标是展示一种使用 SQL-DMO COM 对象脚本化 SQL Server 对象的方法。还有其他方法，但本文的重点是使用 VBScript 中的这个 COM 对象。任何支持 COM 的其他语言都可以用来实现相同的功能。此脚本已在 SQL 2000/2005、MSDE/Express 上进行了测试。

背景

此脚本使用 `SQLDMO.SQLServer2` COM 接口。DMO 代表分布式管理对象。它提供了一个以编程方式管理 SQL Server 的接口。鼓励读者进一步阅读这方面的内容。

Using the Code

此脚本使用 Windows 或 SQL Server 身份验证连接到 SQL Server 数据库。连接成功后，它会遍历数据库中的对象集合并脚本化其中的每一个。

运行脚本的命令行语法如下：

cscript 0g_sqlextract.vbs [server] [database] [output folder] [username] [password]

[server]	服务器位置/IP 地址/实例名称
[database]	数据库名称/初始目录
[output folder]	要写入文件的文件夹（必须存在）
[username]	用户名（可选 - SQL 身份验证）
[password]	密码（可选）

使用 Windows 身份验证的 SQL Express 实例的用法是：

cscript 0g_sqlextract.vbs localhost\my-sql-instance mydatabase c:\sqloutput

使用 SQL Server 身份验证的 SQL 2000 服务器的用法是：

cscript 0g_sqlextract.vbs localhost mydatabase c:\sqloutput sa mypassword

代码分为三个主要部分。有常量声明，用于提供不同的脚本化选项。例如，如果希望脚本先删除对象再创建它。入口点 `Main` 负责参数验证并驱动脚本化对象和 `ScriptObjects` 子例程的调用，该子例程遍历集合中的元素。

在调用 `Script` 方法时需要常量定义。还有其他选项，但这些是本文使用的选项。

'List all Microsoft® SQL Server™ components that 
'depend on the referenced SQL Server component.
const SQLDMODep_Children = 262144
'Apply descending order to returned list.
const SQLDMODep_OrderDescending = 131072
'SQLDMOScript_DRI_Checks, SQLDMOScript_DRI_Defaults, 
'SQLDMOScript_DRI_ForeignKeys, SQLDMOScript_DRI_PrimaryKey, and 
'SQLDMOScript_DRI_UniqueKeys
const SQLDMOScript_DRI_AllConstraints=520093696 
const SQLDMOScript_ToFileOnly = 64              'output file
const SQLDMOScript_OwnerQualify =  262144       'object owner
'Generate Transact-SQL creating the referenced component.
const SQLDMOScript_PrimaryObject = 4
'Include Transact-SQL privilege defining statements 
'when scripting database objects.
const SQLDMOScript_ObjectPermissions = 2
const SQLDMOScript_IncludeIfNotExists =4096     'if exists
const SQLDMOScript_Indexes = 73736              'indexes
const SQLDMOScript2_NoCollation = 8388608       'no collation
const SQLDMOScript_Triggers = 16                'triggers
'Generate Transact-SQL to remove the referenced component. 
'Script tests for existence prior attempt to remove component.
const SQLDMOScript_Drops = 1
const SQLDMOScript_DRI_NonClustered = 4194304
const SQLDMOScript_DRI_Clustered = 8388608
const SQLDMOScript_DRI_Checks = 16777216
const SQLDMOScript_DRI_Defaults = 33554432
const SQLDMOScript_DRI_UniqueKeys = 67108864
const SQLDMOScript_DRI_ForeignKeys = 134217728
const SQLDMOScript_DRI_PrimaryKey = 268435456
const SQLDMOScript_DRI_AllKeys = 469762048
const SQLDMOScript_DRIWithNoCheck = 536870912
const SQLDMOScript_DRI_All = 532676608
const SQLDMOScript_NoIdentity = 1073741824

'folder definitions
const PROC_FOLDER = "\stored procedures"
const VIW_FOLDER ="\views"
const UDF_FOLDER = "\functions"
const TAB_FOLDER ="\tables"

`Main` 子例程读取参数。参数通过 `objArgs` 变量引用，该变量保存一个值数组。前三个参数是必需的。最后两个参数是可选的。创建了 COM 接口的引用。

Set oSql = WScript.CreateObject("SQLDMO.SQLServer2")

如果只传递三个参数，脚本会将 `LoginSecure` 属性设置为 `True`，这表示使用 Windows 身份验证连接到数据库。否则，将使用 SQL 身份验证。连接成功后，使用以下集合调用 `ScriptObjects`：

oSql.Databases(database).Tables
oSql.Databases(database).Views
oSql.Databases(database).StoredProcedures
oSql.Databases(database).UserDefinedFunctions

sub Main
           
   'get the parameter list   

   dim objArgs: Set objArgs = WScript.Arguments

   if objArgs.Count > 2 then
    connString = objArgs(0)   'connection   

    database = objArgs(1)     'database

    folder = objArgs(2)       'output folder      


    dim user
    dim pw

    Set oSql = WScript.CreateObject("SQLDMO.SQLServer2")      
        
    if objArgs.Count > 4 then
        user = objArgs(3) 
        pw = objArgs(4) 
        StdOut.Echo "SQL Authentication - Connection to database"

        oSql.LoginSecure = False            'SQL authentication 

        oSql.Connect connString ,user,pw   
    else
        StdOut.Echo "windows Authentication - Connection to database"
        oSql.LoginSecure = True   'windows authentication 

        oSql.Connect connString   
    end if
                                                
    call ScriptObjects(oSQL.Databases(database).Tables,"TAB",_
                       "Reading tables: ")
    call ScriptObjects(oSQL.Databases(database).Views,"VIW",_
                       "Reading views: ")      
    call ScriptObjects(oSQL.Databases(database).StoredProcedures,_
                       "PRC","Reading procedures: ")      
    call ScriptObjects(oSQL.Databases(database).UserDefinedFunctions,_
                       "UDF","Reading functions: ")      
                    
    oSql.Disconnect     
      
   else
    StdOut.Echo "Usage: sqlextract.vbs [server or sql " & _ 
                "instance name] [database] [output folder]"   
   end if

    set oSql = nothing  
    
    if err.Description <> "" then        
        StdOut.Echo err.Description
    end if

end sub

`ScriptObjects` 子例程遍历列表中的元素。它会检查对象是否为 `SystemObject`。这是因为只需要脚本化用户对象。选项是将常量值传递给 `Script` 方法的组合。这允许我们在脚本中创建其他信息。这些信息可以是约束定义、触发器、检查对象是否存在以及在创建它之前删除它的选项。此子例程还有两个额外的参数 `ext` 和 `msg`。`ext` 参数用作文件扩展名。`msg` 参数用于显示正在生成的状态。生成文件的格式是 _OWNER.OBJECTNAME.EXTENSION_，因此对于属于 _dbo_ 的 `MyProc` 存储过程，生成的脚本将是：_dbo.MyProc.PRC_。

sub ScriptObjects(list,ext,msg)
 
    Dim object
    StdOut.Echo ""
    StdOut.Echo msg 
    
    'set the scripting options

    dim options
    options = SQLDMOScript_Indexes _
            OR SQLDMOScript_Drops _
            OR SQLDMOScript_IncludeIfNotExists _
            OR SQLDMOScript_OwnerQualify _
            OR SQLDMOScript_PrimaryObject _
            Or SQLDMODep_Children _
            Or SQLDMOScript_DRI_AllConstraints _
            Or SQLDMODep_OrderDescending _
            Or SQLDMOScript_ObjectPermissions _
            Or SQLDMOScript_ToFileOnly _
            OR SQLDMOScript2_NoCollation _
            OR SQLDMOScript_Triggers _
        OR SQLDMOScript_DRI_All
    
    For Each object In list
    
        If Not object.SystemObject Then
            StdOut.Echo object.Name
            object.Script options, GetFolderName(ext) + "\" + _
                          object.Owner + "." + object.Name + "." + ext
        End If
    
    Next
    
end sub

有两个辅助函数用于为每种对象类型创建文件夹。`GetFolderName` 函数将文件扩展名映射到预定义的文件夹名称（例如，TAB = Tables）。如果文件夹不存在，则会调用 `CreateFolder` 子例程来创建文件夹。此处的目的是将对象类型分组到单独的文件夹中。这类似于使用 SQL Server 工具呈现对象的方式。

function GetFolderName(ext)

   dim tmpFolder
   tmpFolder = ""

   select case ext
          case "TAB" tmpFolder = TAB_FOLDER 
    case "VIW" tmpFolder = VIW_FOLDER 
    case "PRC" tmpFolder = PROC_FOLDER 
    case "UDF" tmpFolder = UDF_FOLDER 
    end select

    CreateFolder folder + tmpFolder
    GetFolderName = folder  + tmpFolder

end function

sub CreateFolder(path) 
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    if not fso.FolderExists(path) then
        fso.CreateFolder(path)
    end if

    set fso=nothing

end sub

关注点

本文仅涵盖了 SQL-DMO 功能的一小部分。如果目标是实现自动构建和源代码控制流程，本文可以为您提供一些方向。希望对大家有所帮助。

历史

2006年10月1日 - 初始版本
2008年10月5日 - 脚本化表键并为每种对象类型创建文件夹
2009年9月2日 - 更新源代码