如何在IronPython中为Microsoft.SqlServer.SMO.Scripter.Script类定义参数

Question

我想用下面的ironpython代码来编写我的数据库对象：

import sys
import clr

database_name  = r'localhost\SQLEXPRESS'
dir_assemblies = r'D:\programfiles\Microsoft SQL Server\100\SDK\Assemblies'

# Import SMO Namespace
sys.path.append(dir_assemblies)
clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll')
import Microsoft.SqlServer.Management.Smo as SMO


db       = SMO.Server(database_name)
scripter = SMO.Scripter(db)

for database in db.Databases:
    for table in database.Tables:
        # TypeError: expected Array[Urn], got Table
        scripter.Script(table)    

执行这段代码时，我遇到了以下错误：

File "SMOtest2.py", line 18, in <module>
TypeError: expected Array[Urn], got Table

SMO.Scripter.doc给了我以下信息：

Script(self: Scripter, urns: Array[Urn]) -> StringCollection
Script(self: Scripter, list: UrnCollection) -> StringCollection
Script(self: Scripter, objects: Array[SqlSmoObject]) -> StringCollection

我尝试创建一个Array[Urn]或Array[SqlSmoObject]，但都没有成功。

有没有人知道我该如何为SMO.Scripter.Script类创建正确的参数？

我想把下面的VB代码用python写出来。

来源：http://msdn.microsoft.com/en-us/library/ms162160(v=SQL.90).aspx

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Scripter object and set the required scripting options.
Dim scrp As Scripter
scrp = New Scripter(srv)
scrp.Options.ScriptDrops = False
scrp.Options.WithDependencies = True
'Iterate through the tables in database and script each one. Display the script.
'Note that the StringCollection type needs the System.Collections.Specialized namespace to be included.
Dim tb As Table
Dim smoObjects(1) As Urn
For Each tb In db.Tables
    smoObjects = New Urn(0) {}
    smoObjects(0) = tb.Urn
    If tb.IsSystemObject = False Then
        Dim sc As StringCollection
        sc = scrp.Script(smoObjects)
        Dim st As String
        For Each st In sc
            Console.WriteLine(st)
        Next
    End If
Next

Answer 1

我没用过IronPython或SMO，但看起来它需要某种集合。你有没有试过：

scripter.Script(database.Tables)

而不是一个一个表地写脚本呢？

Answer 2

我找到了解决办法：

arg=System.Array[SMO.SqlSmoObject]([table])

完整的脚本如下：

import sys    
import clr    
# import .NET Array
import System.Array

database_name  = r'localhost\SQLEXPRESS'    
dir_assemblies = r'D:\programfiles\Microsoft SQL Server\100\SDK\Assemblies'   

# Import SMO Namespace    
sys.path.append(dir_assemblies)    
clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll')    
import Microsoft.SqlServer.Management.Smo as SMO    


db       = SMO.Server(database_name)    
scripter = SMO.Scripter(db)    

for database in db.Databases:    
    for table in database.Tables:    
        # create a .NET Array as an argument for the scripter
        arg=System.Array[SMO.SqlSmoObject]([table])
        script = scripter.Script(arg)
        #output script
        for line in script:
            print line